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

Question

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.

Answer 1

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