0

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',
);
  • Are you storing this information in a single table? – McNets Mar 22 '18 at 14:01
  • @McNets what information? The product info is in one table and the attributes is in another table. – CelsomTrindade Mar 22 '18 at 14:39
  • Please add your table schema using CREATE TABLE statements. – McNets Mar 22 '18 at 14:40
  • @McNets for products and attributes, right? – CelsomTrindade Mar 22 '18 at 14:41
  • yes, both tables. – McNets Mar 22 '18 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.