Mariadb – How to improve the table design for different types of an entity

If this were my design decision, I would probably go with more of an 'Option C' (modified option a).

First, why not 'Option B':

For one thing, I like the clarity that each product has it's own table affords. If it's all one big table with a field to determine the type, the relation isn't as clear.

For another, the indexing strategy would always require that type field to be listed. Since it's only 4 types, the index cardinality is extremely low (SELECT * FROM product_table WHERE type='X' is basically doing a full table scan anyway)

Option C

• Create a parent table that holds only the columns that all types share
• Create each product type as it's own table with their individual columns, with one extra: A link to the parent table
• Create each 'link' table: Product_Option, Model_option, etc with links to the respective keys.
• For those with reciprocal links (MODEL_OPTION, OPTION_MODEL) go ahead and create those tables as well. This will add clarity in your joins for anyone looking at it.

The downside is the complexity of making sure to avoid orphans when things are updated/deleted, and initially designing the queries that use these tables.

You can reasonably use both approaches. The majority of contemporary SQL databases (mySQL, PostgreSQL, Oracle, SQL Server) will recognize JSON as a data type, and will let you store it as such (that is: it is not just a string, but a string following a certain number of rules).

Things to consider to make your choice:

1. How the different parts of the data are accessed: If, whenever you need to check the main table, you also need to access the surrogate one (to get the remaining information, regardless of which one it might be), then it is a good idea to have all the information together. If most of the accesses to the main table don't need that remaining information, having it in several separate tables makes more sense.

2. Access permissions (privileges): If anyone having access to the main table doesn't need or shouldn't have access to the reamining information, then, it feels more natural (and is probably easier) to have it in two (or more) different tables, and deal with table permissions, instead of column permissions.

3. Programming language ease of handling the JSON data: You can access JSON data from JavaScript, Python, C#, Java, Ruby, PERL... and most (if not all) programming languages. But is not equally easy with all of them. So, consider if the programming language for your project handles JSON easily, or you prefer to get always atomic data (columns with ints, floats, texts, dates and times, ...) from database rows. If handling JSON is not straightforward, choose surrogate tables.

4. Modularity: if you plan to work with different modules (or classes, or ...) for different types of accounts, it may make sense that each module gets a user_id, and it takes care to check/update one table extra_user_info_X by itself (where X is the type of account). This approach would better match the modularity of the application design.

If your database allows for inheritance (like PostgreSQL or Oracle), that might also be a pattern/paradigm that fits your needs, and a complement to using a multi-table approach.