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.