I'm working on an ecommerce database but I'm having difficulties when working with products attributes, such as size and colors. For example, I have this table where I save each product attribute:
product_id | stock | title | value | color | add_price 1 | 1 | Size | S | null | null 1 | 3 | Size | M | null | null 1 | 3 | Size | L | null | 2.00 1 | 1 | Color | Blue | #FFF | null 1 | 2 | Color | Pink | #000 | null ...
It's ok for me to show each type of attribute, size and color. The problem is when I need to create this configuration and associate each of them. For example, What if I have 10 shirts, where 5 of them are size M and of these 5 shirts 4 are blue and 1 is pink? How should I store this in the database in order to create a proper validation on this?
Currently I don't think I'll be working with products that need more than size and color attributes.
When looking online on some stores, what I've seen is that when there is a case like this, there is different products for each color (ex.: Shirt - Black, Shirt - Pink) and then only the size attribute is choosen. I don't know if there is a way to go on this case. Any suggestions on this is welcome.
I'm working with a mysql database and PHP on the backend
As requested in the comments, this is the tables referent to the products and products attributes.
CREATE TABLE IF NOT EXISTS `product` ( `id` int(11) NOT NULL AUTO_INCREMENT, `active` tinyint(1) NOT NULL DEFAULT '0', `stock` int(11) NOT NULL DEFAULT '0', `name` varchar(250) DEFAULT NULL, `description` longtext, `weight` decimal(10,2) NOT NULL, `package_h` decimal(10,2) NOT NULL, `package_l` decimal(10,2) NOT NULL, `package_w` decimal(10,2) NOT NULL, `price` decimal(10,2) DEFAULT NULL, `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), ); CREATE TABLE IF NOT EXISTS `product_attr` ( `product_id` int(11) NOT NULL, `stock` int(11) NOT NULL DEFAULT '0', `type` enum('List','Color') NOT NULL DEFAULT 'List', `title` varchar(50) NOT NULL, `value` varchar(50) NOT NULL, `color` char(7) DEFAULT NULL, `add_price` decimal(10,2) NOT NULL DEFAULT '0.00', );