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.

Layout of product page

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.

Concrete example of layout of product page

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)

Database model

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:

query result

The query:

    departments.department_title, categories.category_title, products.product_title, variations.variation_title, options.option_title
inner join
    product_variations.product_id = products.product_id
inner join
    product_variations.variation_id = variations.variation_id
inner join
    product_variations.product_variation_id = product_variation_options.product_variation_id
inner join
    product_variation_options.option_id = options.option_id
inner join
    departments.department_id = products.product_department_id
inner join
    categories.category_id = products.product_category_id   
    product_svariations.product_id = 1182;

I'd highly appreciate any feedback before I move on.

  • Quick observations: 1. You do not have keys. You have row pointers. For example, if I wanted to, I could insert "Department 1" 1000x into the Departments table. 2. You have redundant relationships between several entities. For example, I could assign a Category to a Product that conflicted with the Department assigned to that Product. 3. You do not have any method to enforce certain requirements through the data model, namely the options for a given product. I'd start by removing the ids, embracing composite keys, then trying to fit things together. – bbaird Dec 15 '20 at 16:46
  • @bbaird Thank you very much. Wrt point 1, I guess I can enforce that department title has to be unique. Wrt point 2 - I see what you mean and I have removed the redundancy. And the other remarks makes total sense and I'll make work of it. Thanks again. – Marius Claassen Dec 16 '20 at 12:17

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.)


(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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.