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:
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:
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:
ID StandardID SubstandardID
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