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.
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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.
|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|
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0