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
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?