snippetMinor
Whether or not to create separate tables for different product types?
Viewed 0 times
tablescreateseparateproductdifferentfortypeswhethernot
Problem
I'm in the process of designing a database and I'm having second thoughts about my initial design decisions...
Product types are as follows... Models, parts, replacement part kits and options.
Option A (first design):
I planned on having separate tables for the above product types. I'd say about 75% of the fields would be the same in each table.
I created each product type as separate tables because of the associations I need to create between them. For instance, a Model can have many options and a option can have many models. An option can also have many parts and a part can have many options... and so on...
Option B:
Instead of having separate tables I could create a table called Product that encompasses model, part, replacement part kits and options. I could have one field called type to differentiate between model, options, etc. I suppose a down side is several fields would never be used (left null) for certain product types. I'm guessing this is where "not best practices" would come into play..
Option B would greatly reduce the complexity of the db design. I also wouldn't have to worry about referencing a bunch of tables when pulling out data for queries...
Product types are as follows... Models, parts, replacement part kits and options.
Option A (first design):
I planned on having separate tables for the above product types. I'd say about 75% of the fields would be the same in each table.
I created each product type as separate tables because of the associations I need to create between them. For instance, a Model can have many options and a option can have many models. An option can also have many parts and a part can have many options... and so on...
Option B:
Instead of having separate tables I could create a table called Product that encompasses model, part, replacement part kits and options. I could have one field called type to differentiate between model, options, etc. I suppose a down side is several fields would never be used (left null) for certain product types. I'm guessing this is where "not best practices" would come into play..
Option B would greatly reduce the complexity of the db design. I also wouldn't have to worry about referencing a bunch of tables when pulling out data for queries...
Solution
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 (
Option C
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.
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.
Context
StackExchange Database Administrators Q#4152, answer score: 9
Revisions (0)
No revisions yet.