I have table product that contains all types of products. enter image description here Each product has a set of specifications unique for that particular product type. For example if the product is smartphone its specifications would include ram, storage size, cpu etc., but if the product is, for example, clothing its specifications would include the type of fabric, size etc. What would be the proper way to design the database? Currently I see two options:

  1. Create a table for each product type. For example smartphones would be in a table named smartphones and clothes would be in a table named clothing.enter image description here
  2. Have generic table product that will contain all types of products and generic table specifications that will contain specifications for all types of products. enter image description here

What would you recommend me? How would you design the database?

  • Neither of these solutions are great. The main options are: Table-Per-Type, where each type gets its own table, but the common product columns would be in a single table with 1:1 FK relationships (and a Type discriminator column). Or Single-Table, where all columns are in a single table with appropriate CHECK constraints. Side note: 9 image columns seems to break normalization rules, you should probably have a separate ProductImage table. Feb 4 at 21:27
  • Thank you very much for the answer and the link. Never heard of normalization rules, I'm googling it now.
    – FAMO4S
    Feb 4 at 21:38
  • Use either solid table which stores NULLs for non-applicable columns or EAV pattern. PS. Normalize photos/images to separate table joined M:N to products table.
    – Akina
    Feb 5 at 6:58


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.