Schema Design Assistance please.

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

General SQL Server Forums

Database Design and Application Architecture

Schema Design Assistance please.

Author

Topic

Gaj
Starting Member

2 Posts

Posted - 2008-03-10 : 12:47:05

Hi All,

Can you tell me how to represent the problem below?

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 Sofa's and Cateogry C maybe Car. There will however be some common data.

So for a TV I may want to store screen size etc.. and for a Sofa if its leather and for cars maybe whethers its a Saloon, 4x4 etc.. The common data maybe Manufacturer, Price etc..Obviousely each peice of info will be of a different data type.

How best do I represent this in a database 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.

To give you an example of what I'm intending on doing..lets look at desktop computers which will have the following product specific info to filter on:
http://computing.kelkoo.co.uk/ctl/do/compareProducts?back=%2Fc-111801-desktop-computers.html&catId=111801&pid1=18445816&pid2=12461415
and for TV's we have
http://audiovisual.kelkoo.co.uk/ctl/do/compareProducts?back=%2Fc-100311823-lcd-tvs.html&catId=100311823&pid1=18052959&pid2=18704336

or
http://shopcompare.eu/ash/search.php?phrase=GPS
and
http://shopcompare.eu/ash/search.php?phrase=palmtops&cid=28

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

Gaj-It.com - Gadget News

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-03-10 : 13:09:56

Performance for what? Updating/Inserting data or retrieving data?! The schema design will be different depending on the purpose of your application. Is this an OLTP or OLAP?

Are you comfortable with the normalisation? If not then please search the web for tutorials about this concept. There are tones of info online!

Good luck!

Gaj
Starting Member

2 Posts

Posted - 2008-03-18 : 05:05:04

Yes I am confortable with normalisation, but I am mainly concerned about retrieval of data...As you can imagine I could potentially end up with a loads of sub types or use the EAV model...or use XML. Now I'm not a swql wiz to be honest I'm more of a beginner, lots of theory no practice but I don't know how best to implement this...

Gaj-It.com - Gadget News

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-03-19 : 06:36:34

If you are designing an OLAP then you need to research and understand the concept of data warehouse database design. Google for star-schema and snowflake-schema. It should begin to make more sense once you've understood the concepts.

Good luck.

mbosch
Starting Member

6 Posts

Posted - 2008-03-27 : 16:23:15

Something like this:

Category
-CategoryID
-CategoryName

Product
-ProductID
-ProductName
-CategoryID

CategoryAttributes
-CategoryAttributeID
-CategoryID
-CategoryAttributeName

ProductCategoryAttributeValues
-ProductCategoryAttributeID
-CategoryAttributeID
-ProductID
-AttributeValue

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources