locked
Enforcing hierarchy in tables RRS feed

  • Question

  • Hi All,

    We are trying to create a table that will show the hierarchy of products within the company. What I'd like to ask is how can we enforce the hierarchy between child and parent if we are also using effective dating within the table (meaning we need to keep a history of all child-parent relationships within the table. For example - product A rolls up to product B between Jan 2010 and July 2010, and then from July 2010 - Dec 2010, product A rolls up to product C).

    How can we enforce that product A is 'valid' (meaning only one row containing product A can be allowed) for any point-in-time?

    Sample table structure

    child ID (int)

    parent ID (int) -- this field will indicate which parent the rolls up to

    effective start date (datetime)

    effect end date (datetime)

    Initially we are going to PK on (child ID, effective start date), but this allows multiple rows for a child to be 'active' at any point-in-time.

    Can this be done within the table? or does it have to be enforced outside the table?

    Thanks in advance.

    Thursday, September 15, 2011 4:53 PM

Answers

  • The way to enforce the parent-child hierarchical relationship in a relational database is to create multiple tables, one for parents and a second for children. The relationship between the two is a foreign key to parent ID in the table for children.

    Trying to manage the data you show in a single table in the antithesis of proper data normalization.

    • Marked as answer by dnaman Thursday, September 22, 2011 1:30 PM
    Thursday, September 15, 2011 5:34 PM

All replies

  • The way to enforce the parent-child hierarchical relationship in a relational database is to create multiple tables, one for parents and a second for children. The relationship between the two is a foreign key to parent ID in the table for children.

    Trying to manage the data you show in a single table in the antithesis of proper data normalization.

    Thursday, September 15, 2011 5:33 PM
  • The way to enforce the parent-child hierarchical relationship in a relational database is to create multiple tables, one for parents and a second for children. The relationship between the two is a foreign key to parent ID in the table for children.

    Trying to manage the data you show in a single table in the antithesis of proper data normalization.

    • Marked as answer by dnaman Thursday, September 22, 2011 1:30 PM
    Thursday, September 15, 2011 5:34 PM
  • So would it look like something like this?

    ChildTable (childID (PK), child name, parentID)

    ParentTable (parentID (PK), parent name)

    Foreign Key from ChildTable.parentID to ParentTable.parentID ??

    I can see that this would enforce that each child must roll up to a parent (ie a child cannot be orphaned and roll up to nothing). Correct?

    thx

    Thursday, September 15, 2011 6:14 PM
  • That's it. Let the foreign key constraint help you manage your data and maintain proper relationships.
    • Proposed as answer by Stephanie Lv Thursday, September 22, 2011 1:11 PM
    Friday, September 16, 2011 12:02 AM