Understanding Table Relationships

You create relationships between tables to prevent redundant data. In most cases, the name of the field is identical in both tables, but this is not always the case. The relationship between two tables is based on both tables having at least one identical field.

For example, in the Infor CRM database, the ACCOUNT and CONTACT tables are related because they each contain an AccountID field with the same value. However, the ACCOUNT table is also related to the USERINFO table through the AccountManagerID field in the ACCOUNT table, and the UserID field in the USERINFO table. Although the field names are different, the ACCOUNT and USERINFO tables are related.

  • In this Help system, related tables are listed in the Related Table and Field column.

  • The related table and field are in blue text allowing a jump to the related table.

  • Relationships explicitly defined in the Application Architect entity model are shown in bold face.

For more information about table relationships, see the Data Relationships topic in the Administrator or Architect help.

Tables can have several types of relationships:

(One) to (One) - For a single record in one table there is only one related record in another table. For example, a contact is related to only one account; therefore, the relationship between the CONTACT and ACCOUNT tables is One to One.

(One) to (Many) - For a single record in one table there are one or more related records in another table. For example, more than one contact can be associated with an account, but at least one account is always associated with a contact; therefore, the relationship between the ACCOUNT and CONTACT tables is One to Many.

(Many) to (Many) - There are many records in one table that relate to many records in another table. For example, a user can have many activities and an activity can have many users. Therefore, the relationship between the USERINFO and ACTIVITY tables is Many to Many. You cannot work with tables that have a Many to Many relationship directly. You must use a link table, an intermediary table that resolves Many to Many relationships by creating a link between the tables. If you need information from the USERINFO and ACTIVITY tables, use the USER_ACTIVITY as the link table to access these tables.