Database structure (mysql): How to manage the set of specifications for every subcategory?

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, ).

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')

The best way would be to create a few tables to store all the different information, have a look at the below implementation:

Category { idCetagory, CategoryName }

SubCetagory { idSubCategory SubCategoryName CategoryID }

Specifications { idSpecification SpecificationName SubCategoryName }

Product { idProduct ProductName SubCategroyID }

ProductSpecifications { idProductSpec SpecificationID Value }

First off, if you're struggling to design the core schema of the database why reinvent the wheel here? Why not use one of the numerous free/open source off-the-shelf cart systems instead?

From personal experience I would suggest looking at CS-Cart Community Edition, but there are many good ones to choose from.

If you don't want to do that then I suggest you start by doing some reading up on relational database design basics.

Then if you want to stick on your current route and keep the schema simple then you could look at implementing a table to store the information as metadata. Create a table something like the following:

ID - used as the row primary key
ProductID - foreign key to the product
Title - the heading of the specification
Value - the value of the specification

This is obviously very simplistic, but should point you in the right direction. It might be better to create a small set of tables to hold defined specifications but I get the impression you are going to need to keep it simple.

I recommend using following table structure:

category (id_category, name)
subcategory (id_subcategory, id_category, name)
subcategory_fields (id_subcategory, field_name, order)
product (id_product, id_subcategory, product_details...)
product_fields (id_product, field_name, field_value)

It's the most flexible approach and allows you to add/remove fields later on easily.

Example:

category: 
   1 "servers"
subcategory: 
   12 1 "rackmountable"
subcategory_fields:
   12 "manufacturer"
   12 "processors"
   12 "rack units"
   ...
product
   100 12 "HP DL380G6"
product_fields
   100 "manufacturer" "HP"
   100 "processors" "1"
   100 "rack units" "2"
   ...

To get the fields for a product:

SELECT pf.field_name, pf.field_value
FROM product AS p 
LEFT JOIN subcategory_fields AS sf 
    ON sf.id_subcategory = p.id_subcategory
LEFT JOIN product_fields AS pf
    ON pf.id_product = p.id_product
    AND pf.field_name = sf.field_name

Break your product into components. So for example I would have:

Manufacturer (id, name) 
Processor (id, p_manufacturer_id, p_name, description) 
Harddisk (id, hdd_manufacturer_id, hdd_name, description) 
RAM (id, ram_manufacturer_id, ram_name, ram_size, ram_type, description) 

... and so on... Then Product would look like:

Product (id, manufacturer_id, processor_id, hdd_id, ram_id .. etc.)

Hope this helps. This of course is "normalization"! While quering may be a pain, this would make searching a lot faster, for example, show me all laptops with 4GB ram made by Sony all you have to do is compare ID's which would be numbers.