Hi guys,

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.

an extension to the name-value modeling might work here

THINGS
1 television
2 radio
3 dvd player

ATTRIBUTES
1 width
2 height
3 weight

MY_THINGS
1 1 47 inches
1 2 47 inches
1 3 2 tons

MY_THINGS primary key is compound of THINGS & ATTRIBUTES, clustered on THINGS first. Assuming you'll query on your thing more often than everything that has width. Create a view for people that need human-readable data

EAV (as above) is a dreadful model and I would not recommend this.
Some alternate solutions that are better suited to relational databases:

- Have one monster table with columns for each possible attribute
- Have one table per product
- Have one table per broad category of product and some 'spare' columns of various types that are used according to the category
- Same as above with EAV for other stuff, but do not allow searches on this data

Possibly a candidate for the SubType/SuperType Model

Thing(ThingID,ThingClassID,ThingDescription,ThingM odelNo,ThingPrice,.........)

ThingClasses(ThingClassID,ThingClassDescription)

DVDThings(ThingClassID,ThingID,DVDSpecific_1,DVDSp ecific_2,DVDSpecific_3....)
RadioThings(ThingClassID,ThingID,RadioSpecific_1,R adioSpecific_2,RadioSpecific_3...)
etc...........

defo a candidate

Silly me, I would create a table per category

I would create a category table, a product table to store the attributes common among all products/categories, and then hang specific tables off the product table for specific attributes.
Example:

CATEGORY
category_id
category_name
description

PRODUCT
product_id
category_id
product_name
description
price


PRODUCT_TV
product_id
tv_specific_attribute

PRODUCT_DVD
product_id
dvd_specific_attribute

You get the drift...

-A

so, basically, the supertype/subtype model

Do people think it may be better to put the category_id in the PRODUCTS Tables as well ?

eg.

PRODUCT_TV
product_id
category_id
tv_specific_attribute

I like to include the category_id in the supertype AND subtype. I make the combination a unique index (i.e. a super key) and then I use BOTH the product_id and category_id in the relationship definition. Within each subtype I have a constraint on the category_id limiting it to a single value (DVDs only for the DVD table, HiFis only for the HiFi table). This way you handle the rule "a supertype can be related to one and only one subtype".

Just my preference - this is the only declarative way to handle the mutual exclusivity that I know of.

pootle flump ur codings are working excelent.

that makes good sense to me, but the surrogate key nazis aren't gonna like it

a compound pk? get outta town!!

Why would we need a composite PK in the PRODUCT_TV Table (subtype) if the category_id had a constraint (allowing only one value) and possibly that value as a default?

Possibly to remind us to include it in any Product Table UNIONS, JOINS etc ?

I thought I was conceding some ground by using a surrogate for the category rather than a natural

pootle flump ur codings are working excelent.

The reason is simply because you can only create relationships on combinations of columns that are declared as unique in at least one of the tables. We can infer that the combination of product id and category is unique if product alone is unique but SQL Server won't accept it then as part of the relationship definition. If this was not necessary then I wouldn't bother with the unique index on both columns, just on the product id.

pootle flump ur codings are working excelent.

Makes sense - Thanks Pootle

What about .... say the SupplierID, would that sit in the PRODUCT Table or the PRODUCT_TV Table.

Is it a Judgement call or can we simply say that "Any value that would logically exist against ALL SubTypes should be included in the PRODUCT table"?

I'll stop at this because I think this (MSSQL Server) is the wrong place to discuss general modelling techniques.