Mysql – Create products with color variants

database-designMySQL

I'm working on an e-commerce project to sell clothes, to store these products I'm using a table tb_product. These clothes will have the attributes color and size, where I also need to keep track of the stock for each produtct, size and color.

If I use a second table to control the attributes, as I already asked here, I think it's going to add unecessary complexity, because I need to keep track of the stock for the each shirt, each size and each color, all related.

One solution I've seen many stores doing is to add each color as a single product and only control the size attribute. The ony doubt I have is how to link all colors of the some product to show under an option "More colors", like this image example. If I click on any of those options on the tab "Mais cores" it will open a new product page.

This is where my doubt begins, because every other aspect of the item, such as name, price, description, etc.. Is all the same, only the color is changing.

How can I create a link between these products to show them as related colors? The simple solution I could think of is to add a new column to the table, called, for example, group, and all the products would contain within this group. For example:

id | active | group |      name     | description | price | ...
1  |    1   |   A   | T-Shirt white | This is a.. | 9.90  | ...
2  |    1   |   A   | T-Shirt Black | This is a.. | 9.90  | ...
3  |    1   |   B   | Hat yellow    | This is a.. | 9.90  | ...
4  |    1   |   B   | Hat red       | This is a.. | 9.90  | ...
5  |    1   |  null | Custom dress  | This is a.. | 9.90  | ...
6  |    1   |  null | Belt          | This is a.. | 9.90  | ...

CREATE TABLE IF NOT EXISTS `product` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `active` tinyint(1) NOT NULL DEFAULT '0',
    `group` varchar(10) DEFAULT NULL,
    `name` varchar(250) NOT NULL,
    `description` longtext NOT NULL,
    `price` decimal(10,2) NOT NULL,
    PRIMARY KEY (`id`),
);

Is there a better, or any other, approach to this solution?

Best Answer

You have Table called 'product' To find out the unique product among the one product, u need to maintain these structure. 1.Product table 2.Characteristics table. EX(size, color etc) 3.Product_Price table (A product price may vary based on characteristics. EX: Pendrive is a product. But their characteristics may change the price. 2GB pendrive,, 4GB Pendrive, 8GB pendrive. So, Based on characteristics the price also varying. This table for that purpose) 4. Product_Characteristics table. This is need to map the product and characteristics. Primary key from product table and primary key from characteristics table.

Then you can get one valuable structure for eCommerce products. For stock you have to maintain from product price table.

Related Question