MySQL: products, attributes, stock levels

Question

For an online store I have

• Product table (id, name, price, description)
• Attribute table (attribute name, attribute value, product_id_fk)

Examples of attribute names include: size, color, and attribute examples: XL, L, red, blue.

What is the best way to store stock levels? The Bag product can be in stock in L / red, but not in XL / red, while it can be in stock for all sizes in blue. A product can have more than two attributes.

A third table is definitely necessary. I think the best way is to have one column per attribute and an extra column for product_id_fk. This would mean that if a new attribute is added for any product, an additional column must be created in the inventory table.

Or is there a better way?

Answer 1

It looks like you have a many-to-many relationship (that is, products can have many attributes, and attributes can be associated with many products). Therefore, your best design is to have a relationship table containing the identifiers 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 element. If you do not, you need to have something that can handle different types of 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

Of course, this has a finite number of attributes, but it is easy to modify the table to add additional attributes as needed.

For example, to add additional types of colors:

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

:

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

, , . (red L), (L XL) . . , red, blue, L XL, color size.