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

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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

Leave a Reply