18

I'm using MySQL. The idea is similar to shopify with a different concept, so users are going to add their own products with multiple types of variants and attributes.

From all the research I've done this seems the most likely solution for me and I'm just wondering if there's anything wrong with the following schema and what are the upsides/downsides?

Thank you

Table: products
------------------------------
| ID | ProductName           |
|----------------------------| 
| 1  | Leather Wallet Case   |
| 2  | Jeans                 |
| 3  | Power Bank            |



Table: products_variants
-------------------------------
| ID | ProductId | ParentId | Variant  | VariantName | SKU  | StockTotal | WholeSalePrice | BuyPrice | OnSale | OnSalePrice |
|---------------------------------------------------------------------------------------------------------------------------|
| 1  | 1         | null     | model    | iPhone5     | SKU  | 10         | 3              | 10       | null   | null        |
|---------------------------------------------------------------------------------------------------------------------------| 
| 2  | 1         | null     | model    | iPhone4     | null | null       | null           | null     | null   | null        |
| 3  | 1         | 2        | color    | Red         | SKU  | 10         | 3              | 10       | null   | null        |     
| 4  | 1         | 2        | color    | Blue        | SKU  | 10         | 3              | 10       | null   | null        |     
|---------------------------------------------------------------------------------------------------------------------------|
| 5  | 2         | null     | size     | M           | null | null       | null           | null     | null   | null        |
| 8  | 2         | 5        | color    | Black       | SKU  | 10         | 3              | 10       | null   | null        |
| 9  | 2         | null     | size     | XXL         | SKU  | 10         | 3              | 10       | null   | null        |
| 10 | 2         | 9        | material | Cotton      | null | null       | null           | null     | null   | null        |
| 11 | 2         | 10       | color    | Red         | SKU  | 10         | 3              | 10       | null   | null        |
| 12 | 2         | 10       | color    | Blue        | SKU  | 10         | 3              | 10       | null   | null        |
| 13 | 2         | 9        | material | Casmir      | null | null       | null           | null     | null   | null        |
| 14 | 2         | 13       | color    | Green       | SKU  | 10         | 3              | 10       | null   | null        |
| 15 | 2         | 13       | color    | Brown       | SKU  | 10         | 3              | 10       | null   | null        |    
|---------------------------------------------------------------------------------------------------------------------------|
| 13 | 3         | null     | null     | null        | SKU  | 10         | 3              | 10       | null   | null        |
2
  • 1
    Click on the 'eav' tag.
    – Rick James
    Dec 11 '15 at 20:09
  • I'm not interested in a full EAV solution. The scheme I've designed it uses some concept of EAV but not fully.
    – lesandru
    Dec 11 '15 at 20:56
7

This is just the info from @lesandru reply, I really find it very useful, so credits to him and @sahalMoidu

Applying normalization to your problem the solution is as given. Run and see it on Fiddle

Fiddle

CREATE TABLE products 
    (
     product_id int auto_increment primary key, 
     name varchar(20), 
     description varchar(30)

    );

INSERT INTO products
(name, description)
VALUES
('Rug', 'A cool rug'  ),
('Cup', 'A coffee cup');

create table variants (variant_id int auto_increment primary key,
                       variant varchar(50)
                       );
insert into variants (variant)
values ('color'),('material'),('size') ;   
create table variant_value(value_id int auto_increment primary key, 
                           variant_id int ,
                           value varchar(50)
                           );

insert into variant_value (variant_id,value)
values (1 ,'red'),(1 ,'blue'),(1 ,'green'),
        (2 ,'wool'),(2 ,'polyester'),
        (3 ,'small'),(3 ,'medium'),(3 ,'large');



create table product_Variants( product_Variants_id int  auto_increment primary key,
                            product_id int,
                            productVariantName varchar(50),
                            sku varchar(50),
                            price float
                            );




create table product_details(product_detail_id int auto_increment primary key,
                             product_Variants_id int,

                             value_id int
                             );

insert into product_Variants(product_id,productVariantName,sku,price)
values (1,'red-wool' ,'a121',50);

insert into product_details(product_Variants_id , value_id)
values( 1,1),(1,4);

insert into product_Variants(product_id,productVariantName,sku,price)
values (1,'red-polyester' ,'a122',50);

insert into product_details(product_Variants_id , value_id)
values( 2,1),(2,5);
0
4

Database Schema for Multiple Types of Products

Solution is here: http://www.codingblocks.net/programming/database-schema-for-multiple-types-of-products/

enter image description here

2
  • 1
    Hello @Cao Phong, where would one handle product quantity? Products that do not possess any variants would probably handle it on the product table, but how about products that have variants?
    – codeninja
    Sep 9 '18 at 6:10
  • 1
    Hello what if the attribute like color ,size, or material or any combinations of them affects the price and like said this would do if you don't care about stock qty
    – Bakly
    Oct 2 '18 at 21:10
3

I've found a similar and better solution on this answered question https://stackoverflow.com/questions/19144200/designing-a-sql-schema-for-a-combination-of-many-to-many-relationship-variation

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .