Database schema design for products depending on the variants and prices

Question

We are in the process of designing a database for an e-commerce application and website. We reached an obstacle that has set us back in searching and experimenting but none of the solutions worked because there will be redundancy in the data.

We have made some designs that we rejected. I will not show all the designs but I will show some of them here and one of the last designs that we were about to implement.

1. This is the first design we rejected because it was over-normalized and it was over complicated.

2. The second design was the same thing.

3. This is the last design we tried but it was over simplified and the redundancy will be a lot.

After that we tried searching and looking for a design that will work and suit our previous design to the other tables. We found one in here we did some modification to the design but also it didn't suit us because the redundancy that will happen with the color_id and size_id and it is not a foreign key.

We want to design the tables that accept these cases:

1. I have a product that have different colors and different sizes also the size doesn't depend on the color or vice-versa and they all have the same price.

2. I have a product where it has different colors and sizes but the sizes depends on the color for example (Color: Red) has (Sizes: S,M,L) and (Color: Black) has (Sizes: M,L) and they have different prices.

3. I have a product Where it has different sizes and the price change depends on the sizes for example product table (Size: S) and the (Price: 50$) but the (Size: L) has the (Price: 100$). But it doesn't have the color variant.

The products will not be inserted by us but by some sellers. The colors and sizes will be all choices in a dropdown-menu. And the sizes will be showed only depending on the categories. For example, Categories Tops will have only the (S,M,L,...) and doesn't have the other sizes

Answer 1

Start with entities and relationships. Each entity will get its own table when you're done. Be hardnosed about understanding your real-world entities and your data-world representation of them.

• Your product is definitely an entity.
• A product variant is also an entity.
• A product has one or more variants. That's a one-to-many relationship. (If the product is something without sizes or colors, the variant is generic.
• Each variant has stock-keeping attributes like sku_number and number_in_stock.

You can't use the product to keep stock. Consider this: your product is a T-shirt. But you don't run out of T-shirts, you run out of small red T-shirts and you have to order more of those from your supplier.

Two entities so far:

product :1 to N: variant.

Let's consider the variant to have a collection of product attributes. Let's make attribute an entity. Each attribute has a type: 'size', 'color', 'accent_color' for example. So your attribute table contains these columns.

attribute_id PK
attribute_type VARCHAR(63)  'size', 'color', ...
attribute_value VARCHAR(63)  '30', 'red', ...

You have a many-to-many join table variant_attribute. The presence of a row in this table means the variant has the attribute.

variant_id    PK 
attribute_id  PK

So, we're up to three entities.

product :1 to N: variant :0-N to 0-N: attribute.

That's done with four tables, product, variant, variant_attribute, attribute

(If you want to treat color and size completely separately, you can do that too. But the scheme I propose is field-extensible: if somebody tells you to sell 'cold', 'warm', and 'hot' variants of something next year you won't have to reinvent the wheel.)

These are the important entities and relationships: they describe the stuff your customers order, and the stuff in your warehouse you have to sell. They need to be simple enough to troubleshoot in production at midnight, and expressive enough to represent your real-world entities. Get that part of the schema right.

Only then add on the categories and sections. I cannot tell from your last diagram what you want there. You probably should revisit your entities for that part of your app.

If you want to everything for sale that's red you can do this:

SELECT p.product_id, 
       p.product_name_ar,
       s.attribute_value size,
       c.attribute_value color,
       v.sku_number,
       v.price
  FROM product p  -- one to many with ...
  JOIN variant v ON p.product_id = v.product_id   -- many to many with ...
  JOIN variant_attribute cva ON v.variant_id = cva.variant_id
  JOIN attribute c   ON cva.attribute_id = c.attribute_id  -- ... color 
                    AND c.attribute_type = 'color'
  JOIN variant_attribute sva ON v.variant_id = sva.variant_id -- ... size
  JOIN attribute s   ON sva.attribute_id = s.attribute_id
                    AND s.attribute_type = 'size'
 WHERE c.attribute_value = 'red';  -- we want the red ones.