Hey,

I'm having an issue with designing my database the right way. The database is for a product review website.

Each review is part of a category such as mobile phones, TVs, Speakers etc

I need to display each review's product specification, for example a one size fits all specification for a mobile phone would include things like, Make Model, Talktime and so on...

The problem i have is obviously each category will have a different specification, how am i ment to store the different specficiations if i have over 100 categories. Surely i'm not ment to create a table for each category?

Any help on this matter would be greatly appriciated.

Hmm i guess no one understands my problem, all i can say is i think i'm in need of a multi dimentional database of some sort (if that exists).

I think the problem is easy to understand but no-one likes to recommend a non relational approach. Personally I'd just put as much as you can into the normal tables and then create something like the following. For those params where there's no valid values then ensure there's a valid type ie (int,date etc) but remember you'll have to check the validity. Always check that the params are valid for a given category.

Tables:

• Category: id, name, parent_category_id
• CategoryParam: category_id, param, type
• CategoryParamValidValues: category_id, param, value
• ProductParams: product_id, param, value

Example SQL

insert Category ( 1, "Mobiles", null )
insert Category ( 2, "Smart phone", 1 )

insert CategoryParam ( 2, "O/S", null )
insert CategoryParam ( 2, "Memory (GB)", "int" )

insert CategoryParamValidValues( 2,"O/S","Android" )
insert CategoryParamValidValues( 2,"O/S","iPhone" )
insert CategoryParamValidValues( 2,"O/S","BlackBerry" )
insert CategoryParamValidValues( 2,"O/S","Windows Mobile" )
insert CategoryParamValidValues( 2,"O/S","Symbian" )
insert CategoryParamValidValues( 2,"O/S","Other" )

insert ProductParams ( 1234, "O/S", "iPhone" )
insert ProductParams ( 1234, "Memory", "16" )

This would be easy to implement and would probably work fine for you but you'll have to implement your own RI as FK won't work for this. The normal danger is that people get very messy with what param are stored and soon get in a mess.

You could make categories inherit parent params (in your UI) and you could also have a sequenceNo in the CategoryParam table in order to sequence what is shown to the user.

Mike