Let's say I want to sell computers and monitors.
Let's say I want the computer to store parameters like Memory (GB), Processor Speed (GHz), hard drive type, hard drive size.
And monitors have parameters like Size (inches), resolution and ratio.
All products have name, price and availability.
What's the best way to store them in MySQL database if later I want to filter it eg. "All products in price range >500$"?
Use an attribute-value table:
CREATE TABLE attributes (
product_id INT, -- foreign key to products table
attribute_name VARCHAR(32), -- size, resolution, ratio, etc.
attribute_value VARCHAR(32) -- value of the attribute
);
Then you can do a query like:
SELECT product_id
FROM attributes
WHERE attribute_name = 'size' AND attribute_value = 32
and you can join this table with the products table to get things like the price.
If you want to combine multiple attributes, see
mySQL - Create a New Table Using Data and Columns from Three Tables
