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.

Related Topics

Query Builder Overview

Table Relation Types

Join Types

 

 

What's New in this Release

 

For a list of new features, see the What's New In This Release topic.

Contact us:

This documentation was developed by Infor CRM User Assistance. For content revisions, questions, or comments, contact the Infor CRM writers at documentation@infor.com.