Database design for an ecommerce website

Question

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:

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.

Answer 1

Your schema looks pretty we normalized, bravo! The only thing that jumps out as me is why store the the variation_type_id on the Products table when you can already get it via joining to the Product_Variations and then Variations table? This is more nitpicky, but that appears to be redundant. (Probably the same question for option_type_id.)

Answer 2

(Partial critique)

• Don't use INT (4 bytes) if a smaller datatype will suffice.
• Don't normalize in cases where simply including the data would not be too bulky (and would save a JOIN)
• I doubt if the only way you will be starting a query is with one product_id. Think about some of the other queries; I worry that they will get quite messy and slow.
• If your data won't fit in RAM, you are likely to have performance issues.

Answer 3

I would prefer eliminating the Products.variation_type_id and Products.option_type_id from Products table. Also I prefer eliminating Product_Variations table. In Product_Variation_Options table, you can have the following columns:

• product_id
• variantion_id
• option_id

The primary key would be composite of 3 columns.

You only need to query Products, Variations, Options, and Product_Variation_Options (4) tables to get your listed result.