Hi All,

I want to be able to store different types of information for products in different categories.

So Category A maybe TV's and category B maybe DVD players, I want to be able to store different types of information on each product in that category. So for TV I may want to store screen size etc.. and for DVD players I may want to store if it supports upscaling etc.

Obviousely each peice of info will be of a different data type but the reason I ask how best to store this is because I will have lots of categories of products and the products will be used for filtering too. So I need to create a solution which is not too costly on performance.

Thanks in advance, any guidance even to any online tutorial would be appreciated.

Best design help ???...

Probably a long way from best, but maybe a simple parent-child relationship? With common product information in the parent table (name, product number, classification...,) and product specific information in the child table, along with the presentation order... (ParentID, PresentationOrder, parameter, parameter description...)

a workable solution,anyway.

Ref Fundamentals of Database Design, by Paul Litwin for starters

There are two basic ways to look at this kind of problem.

If you want to optimize performance for INSERT operations, you can stuff some XML into each row. This is utter anarchy from a data management perspective, but it sure is fast.

If you want to optimize SELECT, UPDATE, and DELETE operations, you can use entity decomposition. This means that each new class of objects needs a new table added to your schema, but it means that you can then manage the resulting data.

-PatP

Thank you both for your suggestions, just want to probe a little further to identify whats best...

With regards to the XML, will this cause performance problems if 100s of people are hitting the db at once searching through the same category although this is interesting and I never considered it ..

To give you an example lets look at for the desktop computers we will have the following product specific info to filter on:

http://computing.kelkoo.co.uk/ctl/do...&pid2=12461415

and for TV's we have
http://audiovisual.kelkoo.co.uk/ctl/...&pid2=18704336


In the second instance, of creating parent-child relationships, we could end up with thousands of tables, is this really the way to do it? have hundreds of tables for category specific details? and you would not know how many fields/params are sufficient.

What do you think Kelkoo does? or look at this site..
for sat navs the have the filtering on the left...
http://shopcompare.eu/ash/search.php?phrase=GPS
and for PDA you also get these..
http://shopcompare.eu/ash/search.php...almtops&cid=28

Thanks again for your help on this...

Looking around and speaking to people I have been told that another option is to have 1 related table to the categories which stores category related details and also another table related to that storing its type. Anybody know any more about this?

Is the xml and related table solution better

In SQLSVR 2005 you can index components of your XML data.

It sounds like thats the best way to do it, fortunately I using sql server so I can use that functionality.

I would be interesting to learn more about how to do this using another table which stores fields related to product and category. Is there anyway of validating the data and does anybody use this? sure people who use mysql to store the data must do this?

Thanks.
Gaj