MySQL: products, attributes, stock levels

Question

For an e-shop I have

• Products table (id, name, price, description)
• Attributes table (attribute name, attribute value, product_id_fk)

Attribute name examples include: size, color and Attribute value examples include: XL, L, red, blue.

What is the best way to store stock levels? A "Bag" product may be in stock in L / red but not in XL / red whereas it may be in stock for all sizes in blue. There may be more than 2 attributes per product.

A third table is definitely needed. I think the best way to go is to have one column per attribute and an additional column for the product_id_fk. This would mean that if a new attribute is added for whatever product, an additional column needs to be created in the Stock table

Or is there a better way?

Answer 1

It looks as though you have a many-to-many relationship (i.e., products can have many attributes, and attributes can be associated with many products). Therefore your best design is to have a relationship table that contains the ids of both the product and the attribute.

products (id, name, price, description)
attributes (id, attribute name, attribute value)
product_attribute (product_id (PK,FK), attribute_id (PK,FK)) 

This works if you have a unique row for each physical item. If you don't then you need to have something that can handle the different types of the product.

product (id, name, price, description)
product_type (id, product_id (FK), count)
attributes (id, attribute name, attribute value)
product_attribute (product_type_id (PK,FK), attribute_id (PK,FK))

PK = Primary Key FK = Foreign Key

Answer 2

Why not create a table like this:

show create table product\G
*************************** 1. row ***************************
       Table: product
Create Table: CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `price` float NOT NULL,
  `color` enum('red','blue') NOT NULL,
  `size` enum('L','XL') NOT NULL,
  `stocklevel` int(11) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Sure, this has a finite number of attributes, but it is not hard to alter the table to add additional attributes as necessary.

For example, to add additional color types:

ALTER TABLE product MODIFY COLUMN `color` ENUM('red','blue','green') AFTER `price`;

and to add an additional attribute:

ALTER TABLE product ADD COLUMN `condition` ENUM('good','bad','ugly') AFTER `size`;

By the way, I think the attributes table has a problem. A product can have attributes (red and L), but (L and XL) would not make any sense. The attributes table does not stop such data from being entered. I think the true attributes are not red,blue,L and XL but rather color and size.