Share via


Cardinality in Valid Time State Table Relationships

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.

Gg864901.collapse_all(en-us,AX.60).gifM: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

Gg864901.collapse_all(en-us,AX.60).gifM: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.

Gg864901.collapse_all(en-us,AX.60).gif1: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

Gg864901.collapse_all(en-us,AX.60).gif1: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.

Gg864901.collapse_all(en-us,AX.60).gif1: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

Gg864901.collapse_all(en-us,AX.60).gif1: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.

See also

Valid Time State Tables and Date Effective Data

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.