I am designing an ecommerce database. The database must be able to deal with product variations and for each variation, also product options.
Below is the desired layout for the product page. The
Variation Type is a title which describes the type of variations that are available specific to the product.
Option Type is a title which describes the type of options within the product variation that are available.
As a concrete example, let's take a Duvet Set as an example. The layout is below.
The admin (who lists the products) chooses for a specific product that they want to set
Variation Type to Duvet Size and
Option Type to Duvet Colour. Therefore, the possible product variations (which will be listed in the select dropdown) are, for example, Single, Double, Queen and King. And, let say, for Single the product options will Red, Green and Blue, while for Double, the product options will be only Red and Blue. And so forth for the other product variations.
Below is a first iteration of the relevant part of the model. A brief motivation/explanation for each table:
Departments and Categories: Each department (eg. Bedroom) can have one or many categories (Eg. Duvet Sets, Pillows, etc.), but each category belongs to only one department. Thus the obvious one-to-many relationship.
Variation Types: The reason for this table is for the admin to be able to specify, for a specific product, how product variations should be distinguished. So instead of a singular hard-coded "Size", the admin can specify "Material type" or "Design type", etc.
In terms of the relationship between Variation Types and Products, Variation Type can be associated with many Products, but a Product belongs (or has) only 1 Variation Type. For example, "Royal Duvet Set" can be varied by "Duvet Size", while "Sky Blue Duvet Set" can be varied by "Duvet Design".
- Option Types: The reason for this table is similar as that of Variation Types - for the admin to be able to specify, for a specific product, how product options should be distinguished. So instead of a singular hard-coded "Colour", the admin can specify "Duvet Colour" or "Duvet Thread Count", etc.
In terms of the relationship between Option Types and Products, option type can be associated with many products, but a product belongs (or has) only 1 option type. For example, "Royal Duvet Set" can be have the option type "Duvet Colour", while "Sky Blue Duvet Set" can have the option type "Duvet Thread Count".
Variations will contain all the values for the different variations. For example: Single, Double, Queen, King, Cotton, Polyester, etc.
Product_Variations: Since products can have many variations (eg. Single, Double, etc) and a specific variation can belong to many products (eg. to two different duvet products), this table is just the linking table to deal with the many to many relationship.
Product_Variation_Options: The Product_Variations table defines the different variations that are available for every product. Since for every "product variation" there can be different product options, the Product_Variation_Options table defines the different options which are available for every product variation (and is the linking table between Product_Variations and Options)
So this is the design I have come up with and was wondering if the database geeks can give me their highly respected opinion on it. Is there perhaps a better way to do the design which will make queries a bit easier and elegant? Or does the design in some way have inherent flaws.
I inserted some dummy data into the tables. I then performed the following query. The result of the query shows that for the product "Sky Royal Duvet Set", There are two variations (Single and Double). For Single, there are options Red, Green, Blue and Yellow, while for Double, there are only Red and Blue.
The result of the query:
select departments.department_title, categories.category_title, products.product_title, variations.variation_title, options.option_title from product_variations inner join products on product_variations.product_id = products.product_id inner join variations on product_variations.variation_id = variations.variation_id inner join product_variation_options on product_variations.product_variation_id = product_variation_options.product_variation_id inner join options on product_variation_options.option_id = options.option_id inner join departments on departments.department_id = products.product_department_id inner join categories on categories.category_id = products.product_category_id where product_svariations.product_id = 1182;
I'd highly appreciate any feedback before I move on.