Inventory management with stock options

by

I think the draft model (following 6NF and 3NF) will help you.
I simplified naming convention by removing ‘shop’ keyword.
(Also shop entity may leads a separate concept AKA SaaS)

SqlFiddle Demo

enter image description here

About the questions in the comments:

Is it possible to have a unique product ID

Yes, it is a common pattern to use surrogate identifier on your tables. As you may see in the article, that will come with its pros and cons.

For example, in the question, you will see that primary key of ProductSpecification table is a composition of ProductTypeOptions, OptionValue and Product foreign keys.
In the mean time primary key of other tables like OptionValue is a composite key (OptionId + ValueName)
It looks like life will be more easy to have an ID field in every table as the primary key, yes it is but as a database designer you will loos something valuable, business logic.

In the current design you can have these constraint in Product-Specification table, they will show part of your business logic:

  • Check constraint on ProductSpecification {OptionValue.optionId =
    productTypeOption.optionId}     that will prevent a value like “White”
    being assigned to “Size”.
  • Check constraint on ProductSpecification {product.productTypeId =
    productTypeOption.productTypeId}     that will prevent a product like
    “Nike” being assigned to productSpecifications of “Cars”.

If you use surrogate identifier you can not have these type of constraints inside your data base (try this).
Extra work will be needed to be done inside you application implementation to gain them.
BTW use surrogate identifier, check data consistency, if more interested see choosing a Primary Key: Natural or Surrogate.

Where should the base price, stock, and surcharge go?

It seems that “Mens Shoe” of “Nike” needs to have price, stock and surcharge, so they are natural property of Product table.

More Related Contents:

  1. Modeling Product Variants
  2. Proper database model for a user feedback system (an interesting case)
  3. What does character set and collation mean exactly?
  4. Implementing Comments and Likes in database
  5. Difference Between Schema / Database in MySQL
  6. Auto Generate Database Diagram MySQL [closed]
  7. MySQL stored procedures use them or not to use them
  8. In SQL, is it OK for two tables to refer to each other?
  9. Relational database design multiple user types
  10. How to design a multi tenant mysql database
  11. DOs and DONTs for Indexes [closed]
  12. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  13. How do I show the schema of a table in a MySQL database?
  14. Difference between two table structure
  15. MySQL query, MAX() + GROUP BY
  16. Strings as Primary Keys in MYSQL Database [closed]
  17. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  18. How to store a one to many relation in MySQL database?
  19. How to do a batch insert in MySQL
  20. Count the occurrences of DISTINCT values
  21. What are the known ways to store a tree structure in a relational DB? [closed]
  22. Is there an SQLite equivalent to MySQL’s DESCRIBE [table]?
  23. When to use MyISAM and InnoDB? [duplicate]
  24. How to insert a file in MySQL database?
  25. Foreign key to one of many tables?
  26. Rename a table in MySQL
  27. What’s the recommended way to connect to MySQL from Go? [closed]
  28. How to find all the relations between all mysql tables?
  29. How to speed up sql queries ? Indexes?
  30. Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user ‘root’@’localhost'(using password:YES)

Leave a Comment