How to design table that has different columns for different category?

Question

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?

Answer 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 valid¹.
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

¹ 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).