Cardinality in Valid Time State Table Relationships

Article
05/18/2015

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

Valid time state tables are used to track the effective dates for the relationship between two entities. You can design the indexes on your valid time state table to control the cardinality of the relationship.

Cardinalities of Relationships

The following table lists the three cardinalities of relationships that can exist between two entities at one moment in time. The cardinalities are described with the example of employees that have been assigned to projects. For valid time state tables, the cardinalities are in terms of any single moment in time.

Name	Symbol	Comments
many-to-many	M:M	At one moment in time, any one project can have many employees assigned to it (or have no employees). Also, any one employee can be assigned to many projects (or be assigned to no projects).
one-to-many	1:M	At one moment in time, any one project can have many employees assigned to it (or no employees). But any one employee can be assigned to at most one project (or be assigned to no project).
one-to-one	1:1	At one moment in time, any one project can have at most one employee assigned to it (or no employees). Also, any one employee can be assigned to at most one project (or be assigned to no project).

A valid time state table that enforces a 1:1 cardinality can have multiple rows for a given pairing of a particular employee to a particular project. However, each such row would have to refer to a different date range.

Many-to-Many

This section displays an example of M:M data. Then it describes the alternate key index that provides a M:M relationship in a valid time state table.

M:M Data

The following table shows example pairings of employees and projects in a M:M relationship. Notice that there are days when each employee is on two projects.

Employee ID	Project ID	ValidFrom	ValidTo
em11	pr99	2001-01-22	2002-02-22
em11	pr888	2001-07-23	2004-04-23
em11	pr99	2003-03-24	2004-04-24
em222	pr99	2001-01-11	2003-03-11
em222	pr888	2002-02-13	2004-04-13

M:M Index

The following list displays the fields that would belong in a unique index for this M:M relationship:

EmployeeID
ProjectID
ValidFrom
ValidTo

Note

The ValidFrom and ValidTo fields must be included in the index when a valid time state table is involved.

One-to-Many

This section displays an example of 1:M data. Then it describes the alternate key index that provides a 1:M relationship in a valid time state table.

1:M Data

The following table shows example pairings of projects and employees in a 1:M relationship. On any one date, each project can have many employees assigned to it, but each employee can be assigned to at most one project. The data shows that in August 2001 the pr99 project had two employees working on it, while each employee was working on only that one project. Later both employees were reassigned to the pr888 project.

When a relationship between an employee and a project has no known end date, the ValidTo field is set to the maximum date. For more information, see maxDate Function, or see the DateTimeUtil::maxValue Method.

Employee ID	Project ID	ValidFrom	ValidTo
em11	pr99	2001-01-15	2002-01-15
em11	pr888	2002-01-16	2154-12-31
em222	pr99	2001-07-15	2002-01-15
em222	pr888	2002-01-16	2154-12-31

1:M Index

The following list displays the fields that would belong in a unique index for this 1:M relationship:

EmployeeID
ValidFrom
ValidTo

One-to-One

This section displays an example of 1:1 data. Then it describes the alternate key index that provides a 1:1 relationship in a valid time state table.

1:1 Data

The following table shows example pairings of employees and projects in a 1:1 relationship. Any one project can have at most one employee assigned to it, and any one employee can be assigned to at most one project. Notice that em11 and pr888 are paired more than one time, because each pairing applies to a different date range.

Employee ID	Project ID	ValidFrom	ValidTo
em11	pr888	2008-01-01	2008-12-31
em11	pr99	2009-01-01	2009-12-31
em11	pr888	2010-01-01	2010-12-31
em222	pr99	2010-01-01	2010-12-31
em222	pr888	2009-01-01	2009-12-31

1:1 Index

Two alternate key indexes are needed to enforce a 1:1 relationship. Each index enforces a different 1:M relationship. Their combined effect is to enforce a 1:1 relationship.

One of the indexes has the following fields:

EmployeeID
ValidFrom
ValidTo

The second index has the following fields:

ProjectID
ValidFrom
ValidTo

For more information about alternate key indexes on valid time state tables, see Walkthrough: Creating a Valid Time State Table.