1

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.

1 Answer 1

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
1
  • 1
    This is how I would design it too.
    – J.D.
    Mar 16, 2022 at 0:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.