I have a family of products that currently houses 3 product lines. All 3 product lines share the same 4 options, but on top of those, each product line has 1, 2, or 3 different options respectively, individual to that product line. To illustrate:

| Product Family:                                   |
| Product Line 1 | Product Line 2 | Product Line 3  |
| * Base Option1 | * Base Option1 | * Base Option1  |
| * Base Option2 | * Base Option2 | * Base Option2  |
| * Base Option3 | * Base Option3 | * Base Option3  |
| * Base Option4 | * Base Option4 | * Base Option4  |
| * Ind Option X | * Ind Option Y | * Ind Option A  |
|                | * Ind Option Z | * Ind Option B  |
|                |                | * Ind Option C  |

How can I model those in relational database?

What I have now is I have a table called category = {Product Line 1, Product Line 2, ..} and product = {Product Line 1 - Model A, Product Line 1 - Model B, ...}

enter image description here

  • STI, one-table, key-value – Dennis Apr 28 '17 at 14:12

This sounds like a valid case for single-table inheritance (STI). (Assuming that a "valid" case for STI exists, see my counterpoints at the end.)

The basics of STI are:

  1. Create a table with the most abstract name of all the inheriting classes.
  2. Put all of the columns, both shared and unique, in this table.
  3. Add a column called object_type (or whatever name you prefer, I dislike type because it is used in some languages to get the type of an object and can cause naming collisions).
  4. In your code, use the object_type column to determine which columns are applicable to the record, and null out the others.

However, having used STI before, I'm personally not a fan of it. Cons of STI include:

  1. Non-normalized tables. Step 4 above should raise a red flag if you're database-savvy, as it's an immediate indicator that this table is not normalized well.
  2. The data are no longer as self-describing as in a normalized table set. It's difficult to look at this single table and determine what is actually in it. Null fields take on new ambiguity. Are these values null because they're not applicable to the record, or because they are applicable but we have no values for them?.
  3. Refactoring out of STI is far more difficult than setting up multiple tables for the data in the initial design. It is also fraught with risk and subtlety, since you have to migrate old data into the new tables. For example, if you use an ORM for the data migration, it's quite possible that the created_at field in the new tables could show the current date for all the re-created records instead of the original date. And regardless of whether you use an ORM, this will also regenerate all auto-generated surrogate keys (if you're using them) and could break external integrations if those keys are exposed or saved anywhere (although that's a bad idea in itself, see the "Don't naturalize surrogate keys" section here). This and other possible "gotchas" could easily arise.
  4. As you increase the number of object_type supported by the single table, all of the above cons get worse.

STI does work, but it's essentially hacking the data model to make it easier to code against rather than properly configuring your application/ORM to use different tables.

The alternative to STI (and IMO the best solution to this problem) is to use one table for the shared attributes and other tables for the unique attributes. For your case:

Tables (name them whatever makes the most sense):

  1. shared_product_line_options containing Base Options 1-4
  2. product_line_one containing Ind Option X
  3. product_line_two containing Ind Options Y and Z
  4. product_line_three containing Ind Options A, B, and C

Put a foreign key in each product_line table that points to the shared_product_line_options table.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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