数据库结构（mysql）：如何管理每个子类别的规范集？

Question

I'm building an online store using PHP and MySQL and I ran into a big dilemma. The store has about 50 sub categories for products like: Notebooks, Netbooks, HDD, RAM, Software, Games etc) which are divided into 8 main categories (like: Laptops [Notebook, Netbook...etc]).

So now for every product, the client wants to manage the specification fields or attributes. But there are 50 set of specifications that differs from one another. How to do this basically?

I thought I'll have to make 50 tables in database with specific fields. Another idea was to make 8 big tables that contains all the specification fields for all it's subcategory and fill it up with 'null' if the specification doesn't apply to that product... and this is wrong because when a product is inserted I don't know how to build the insert procedure to do this

INSERT INTO table_name VALUES ('','','','','','some specification','','some specification'....etc)`

How to manage the set of specifications for every subcategory?

Specifications look like this:

• Manufacturer : Sony
• Processor : Intel Core i3
• Memory : 3 GB DDR3

Specifications need to be created by the store owner... He want to set the specifications for every subcategory ... Like: The notebooks need to have this specification (manufacturer, processor, memory...etc), the hdd need to have this spec (capacity, rpm, ..etc) ... so basically he wants to create specifications and this is way complicated ... if the specifications were fixed the problem was gone.

and every product have different specifications ... (not only the name of the specification but the specification itself. like video cards, ram, hdd, ).

Answer 1

I think it's better to have separate attributes table like this:

create table attributes(
    id bigint not null AUTO_INCREMENT PRIMARY KEY,
    attribute_name varchar(300),
    #any other field definitions, like data type, allowed range, etc.
)

And linking table category_attribute, just like that:

create table category_attribute(
    category_id int references category(id),
    attribute_id int references attributes(id),
    str_value varchar(1000),
    #if you are going to have different data types for each attribute you'll add it here
    #int_value int,
    #date_value timestamp ...
)

So you'llhave very flexible architecture that will allow you add/remove/modify attributes and category=>attribute relationships easily.

SQL for retrieving all attributes that apply to category with id =1:

select * 
from attributes a
join category_attribute ca on ca.attribute_id = c.id
join category c on c.id = ca.category_id
where c.id = 1 #or any other condition here

Also this will reduce amount of data stored.

Live example (read your question carefully and slightly changed the schema, I believe changes are obvious):

category(1,'Computers')
category(2,'Cell Phones')

attribute(1, 'Manufacturer')
attribute(2, 'Cost')
attribute(3, 'GSM standards')
attribute(4, 'Hard Disk capacity')

category_attribute(id=1,category=1,attribute==1)
category_attribute(id=1,category=1,attribute==2)
category_attribute(id=1,category=1,attribute==4)
category_attribute(id=1,category=2,attribute==1)
category_attribute(id=1,category=2,attribute==2)
category_attribute(id=1,category=2,attribute==3)

product(1, 'iMac', category = 1)
product(2, 'iPhone', category = 2)

product_attribute(product_id=1, category_attribute_id=1, 'Apple')
product_attribute(product_id=1, category_attribute_id=2, '$300')
product_attribute(product_id=1, category_attribute_id=4, '500Gb')
product_attribute(product_id=2, category_attribute_id=1, 'Apple')
product_attribute(product_id=2, category_attribute_id=2, '$200')
product_attribute(product_id=2, category_attribute_id=3, 'GSM 900/1800/1900')