0

I need to design a database table where the columns will be changed on different category: System, User Input

When the category is System, the columns are:

id | orderNo | name | category | status

When the category is User Input, the columns are:

id | orderNo | filename | state_of_charge | capacity | size| resistence | category | status

I have a design in mind, which is to combine all columns into one single table instead of separate to different tables based on category , it is easier for me to do searching(based on category), but my guts feeling is telling me that this design is not pretty, for example, columns will have null values for different category.

Is there any better design?

2
  • Of course. Two separate tables. Aug 9 '21 at 14:21
  • One entity - one table... take a look here (and links within) to see what happens when people try and get around this one! Aug 9 '21 at 14:22
1

You must split the entities. This is a classic case of exclusive subtypes.

Let's review what happens if you create a single table:

  1. Your data model is now opaque - no one can examine your tables and correctly surmise which columns should be populated and when (as subtype-specific attributes must be marked as NULLable). If there need to be FK constraints to other entities, NULL foreign key references are not valid1.
  2. If relationships to other entities exist for some subtypes but not for others, you have no way of explicitly enforcing this with a PK-FK constraint. You will have to rely on a trigger for each table.
  3. If you need to add more subtypes, you must alter the table structure and create yet more columns that do not apply to most rows.

So theoretically you could make this work with a series of triggers, complex constraints, and some transactional logic but that is more work, prone to errors, and decidedly not the right pattern. But it puts to rest the idea that a single table is "simple".

What happens if you create a table for each subtype:

  1. The data model is more easily inferred from the table structure.
  2. The table for each subtype behaves exactly as a normal table would and PK-FK relations can be established based on subtype.
  3. New subtypes can be added as needed without requiring modifications to existing tables.

To do this correctly you will need to create a trigger for each subtype to ensure the Category on the supertype matches.

See:

https://stackoverflow.com/questions/4896831/how-to-implement-referential-integrity-in-subtypes

https://stackoverflow.com/questions/4011956/single-fixed-table-with-multiple-columns-vs-flexible-abstract-tables/4013207#4013207

https://www.softwaregems.com.au/Documents/Article/Database/Relational%20Model/Subtype.pdf

1 While some "databases" allow you to create a nullable FK constraint, this violates the entire relational concept of a foreign key and generally isn't a good idea. There are other ways to implement optional foreign keys (of which subtypes are one).

2
  • thanks for the reply, if there is no common fields between System and User Input, means no supertype, would you recommend to split it in different tables also.
    – hades
    Aug 9 '21 at 15:56
  • Entirely distinct entities can still result in a supertype/subtype situation if there are common relationships throughout the data model, not just attributes. For example. Cars, Boats, and Buildings are completely different entities, but on an insurance policy I wouldn't maintain separate Coverage and Premium entities for each.
    – bbaird
    Aug 9 '21 at 16:21

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.