Data Relationships
A relational database contains multiple tables. The structure of the database is defined by how these tables relate to one another. Since data is acquired based on table relationships, it is important to understand the relationship between tables before adding custom tables and fields to the database. It is also helpful when creating queries for lookups, groups, subscription rules or templates, and so on.
When you create a custom (user-defined) table, the following fields are automatically added:
- CreateUser
- CreateDate
- ModifyUser
- ModifyDate
Because each table must also have at least one primary key, Infor CRM adds the primary key field of the custom table (if the table has a One to Zero, One, or More relationship to any other table), and the primary key field of any associated table. User-defined fields can be added as necessary.
Table Relationships
The relationship of one table to another is based on both tables containing at least one identical field. In most cases, the name of the field is identical as well, but this is not always the case. For example, the ACCOUNT and CONTACT tables are related because they each have a field named AccountID 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 these tables have different field names, their values are the same.
The connection between two tables is called a join and is established using a field that exists in both tables. When two tables are related, each table can have a different relationship. Table relationships are important when creating queries. A query using two tables can return different results depending on which is the source table in the relationship.
