How to model data which sometimes (but not always) has a subsection?



  • I'm trying to help my wife create a schema for a project of hers, and I'm somewhat stumped by some of her data and how to model it.

    She has a bunch of "standards" that go in to a database. Those are fairly simple. Something like:

    Standards
    

    ID Code Description


    1 7DF1 Description of some standard blah
    2 7DF2 Description of some other standard asdf
    3 8A33 Yet another description

    However, some of these "standards" have a kind of "substandard". So 8A33 might have parts a, b, and c which need to be separately referenced.

    So it would be easy to create a Substandards table which looks like this:

    Substandards
    

    ID StandardID Code Description


    1 3 a Description of 8A33 part a
    1 3 b Description of 8A33 part b

    But now other tables need to reference a standard, which could be a normal standard or a substandard. I don't know how to keep this in normal form. All I can think of is to do it like this, but this seems wrong:

    SomeOtherTableThatReferencesStandards
    

    ID StandardID SubstandardID


    1 1
    2 2
    3 3 a
    4 3 b

    The presence of null data in the SubstandardID field tells me I'm likely doing something wrong, but I can't figure out a better way to do this. I considered having a field that tells you if the ID was a Standard or Substandard, but then I can't really maintain referential integrity (no foreign key).

    I know this is kind of vague because I don't have real data here, but I'm thinking this is likely a common problem that is easily solved, and I'm hoping someone recognizes it.



  • Consider a parent_id column to use for hierarchy. This would be similar to the common concept of an employee table. With an employee structure, you would not create a table for reach tier of employment.

    In this structure, all standards would be the in same table. However, sub-standards would have a value in the parent_id column pointing them back to the Standard they are a child of. Below is an example of how the data would be stored. Any standard with a NULL parent_id can be considered a top level standard.

    ID ParentID Code Description
    1 NULL 7DF1 Description of some standard blah
    2 NULL 7DF2 Description of some other standard asdf
    3 NULL 8A33 Yet another description
    4 3 a Description of 8A33 part a
    5 3 b Description of 8A33 part b



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2