Help with Schema Design - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with Schema Design

Author

Topic

SamIAm
Starting Member

3 Posts

Posted - 2003-01-30 : 19:13:13

Hi

I need some advice on some database design. I have a
Product table that contains categories of products.
i.e. Digital Cameras, Monitors, Speakers.

These categories have different attributes and I need to allow a
user to search/compare on these attributes.

I have designed a Product table that holds all basic
product info like:
ProductID [pk]
CategoryID [fk]
SKU
Name
Price

I also have created seperate attribute tables like
Product_DigitalCameraAttribute or
Product_MonitorAttribute that contains attribute info
specific info relating to Digital Cameras:
[Product_DigitalCameraAttribute]
AttributeID [pk]
ProductID [fk]
Pixels
BatteryLife

I have to check what category the user has chosen before I
know what attribute tables to join on. I guess this needs to be done in my business layer before executing stored procedures.

Is this the most efficient way of doing this?

Regards,

S

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-30 : 19:57:47

You could add a column to each table that has the category id used in the product table.

Then use a view to combine all the separate attribute tables so that you can join on the view on product id and/or category id.

SamIAm
Starting Member

3 Posts

Posted - 2003-01-30 : 20:35:53

quote:

You could add a column to each table that has the category id used in the product table.

Then use a view to combine all the separate attribute tables so that you can join on the view on product id and/or category id.

Thanks for the reply. Could you elaborate a little more?

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-30 : 20:52:22

Take a look at this article.

http://www.sqlteam.com/item.asp?ItemID=684

Let the view figure out what table the data is comming from by joining on the view and filtering on category id.

SamIAm
Starting Member

3 Posts

Posted - 2003-01-30 : 21:09:33

quote:

Take a look at this article.

http://www.sqlteam.com/item.asp?ItemID=684

Let the view figure out what table the data is comming from by joining on the view and filtering on category id.

Thanks again. I have had a look at the article. It seems that that scenario only works when there are multiple tables with exactly the same columns. My question relates to having differeny columns in each attribute table.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-30 : 22:21:53

It would be better to modify the design so that you are storing an attribute name and its value, one row per attribute, something like:

ProductID  Attribute    Value
123        Pixels       1000000
123        BatteryLife  6

That way you can keep all attributes in one table, and you don't have to modify its design to add new attributes; just add another row with the right values.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources