How to query a normilized DB - 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

Transact-SQL (2000)

How to query a normilized DB

Author

Topic

MrKicks
Starting Member

6 Posts

Posted - 2004-10-02 : 03:22:28

I am somewhat familiar with a query on a single table but I just inherited a DB that is normilized to 18 tables. I need to pull out the information from several of these tables into one row.

Each Product is available in multiple colors, sizes, materials... and every option is in its own table referenced by an ID

Product table:
Product_ID
Product_name

Color_Conector table:
Product_ID
Color_ID

Color table:
Color_ID
Colors

With these tables I can successfully pull the following with an inner join:

Product1 red
Product1 Blue
Product2 red
Product2 Blue

But when I try to join a forth table I get:

Product1 red small
Product1 red medium
Product1 red large
Product1 blue small
Product1 blue medium
Product1 blue large
Product2 red small
Product2 red medium
Product2 red large
Product2 blue small
Product2 blue medium
Product2 blue large

All I want to see is:

Product1 red,blue small,medium,large

What keywords should I be looking to learn about?

Any sendoff in the right direction would be much appreciated!

MrKicks

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-10-02 : 04:58:21

The fourth table is obviously size. How are you joining this table? If there is a Size_Connector table (corresponding to the Color_Connector table in your three-table query), you should be using this as an intersection between your Product and Size tables.

Mark

MrKicks
Starting Member

6 Posts

Posted - 2004-10-02 : 16:27:28

There is a Size_Connector table and a sizes

The join is as follows (go easy, I'm new to this!)

SELECT dbo.products.name, dbo.features_Connector.description, dbo.colors_Connector.color_name, dbo.sizing_Connector.name AS Expr1
FROM dbo.products INNER JOIN
dbo.features ON dbo.products.product_id = dbo.features.product_id INNER JOIN
dbo.features_Connector ON dbo.features.features_id = dbo.features_Connector.features_id INNER JOIN
dbo.colors ON dbo.products.product_id = dbo.colors.product_id INNER JOIN
dbo.colors_Conector ON dbo.colors.color_id = dbo.colors_Connector.color_id INNER JOIN
dbo.sizing ON dbo.products.product_id = dbo.sizes.product_id INNER JOIN
dbo.sizes_Connector ON dbo.sizes.size_id = dbo.sizes_Connector.sizes_id

The undesired results I get are as follows:
Product1 feature1 size1 Color1
Product1 feature2 size1 Color1
Product1 feature3 size1 Color1...

And so on through all the features then:

Product1 feature1 size1 Color2
Product1 feature2 size1 Color2
Product1 feature3 size1 Color2...

And so on through all the features with the 2nd color, then all the features with the 3rd color... giving me every possible combination of the options as oposed to just a product and options available

My goal is to have:

Product1 fearure1,feature2,feature3 size1,size2,size3 color1,color2,color3

It seems that each additional join is based on the previous join and I THINK I want to join each connector and option table based only on Product_ID

Am I close?

Thank you,
MrKicks

slacker
Posting Yak Master

115 Posts

Posted - 2004-10-02 : 23:08:27

Umm... this looks like you want to combine rows into a single column. This isnt something that is really supported by T-Sql. YOu can do it though. You may need to run loops on your color_connector table and size_connector tables. You could use a udf that returns a concat'd string.. but I garantee you that will suffer a performance penalty. Honestly... If you really, really cant sort these rows on the client... because you need a list... I would have a denormalized column that had the comma seperated list of colors or size or whatever. You could still have the flexability of the normalized tables... just put a trigger on your size_connector and color_connector tables... whenever a row is inserted or deleted on those columns you could generate a comma list of the colors or sizes and put them with the product. Thats just my opinion. Otherwise your looking at some looping.. cursor or something else.

MrKicks
Starting Member

6 Posts

Posted - 2004-10-03 : 00:41:37

So my undesirable results may be the correct results after all and I should be sorting this out on the client?

So I guess I should take a couple steps back and ask what I should be doing overall. I keep reading tutorials on how important it is to normalize the data but I miss the ones on how to use the data after this is done.
the db is ultimately running behind an asp site, never having used multiple tables on a site I was planning on taking the data from this normalized db once and put it into a denormalized db of one table so I can at least work with it in light of my limited knowledge of this stuff. And as usual I am over my head and under a deadline, These always seem to be the times we get most creative and subsiquently post most of our messages!

Any advice from those who have traversed this ground before is more than welcome.

Thanx,
MrKicks

slacker
Posting Yak Master

115 Posts

Posted - 2004-10-03 : 01:28:41

Well theres nothing wrong with normalizing your db. You have the right idea. Most of the time its better to be normalized then not normalized because of its flexibility. I may have had one table instead of all your connector table... maybe something like this


create table Clothing
(
   size_id          int,
   color_id         int,
   product_id       int,
   number_in_stock  int
)

But thats irrelevant really... you still would have the same problem. The problem you have here is common... you need a dynamic cross tab.. There just really isnt an easy solution for it... there are products you can buy for it and there are a few tutorials about them here at sql team...

If i were in your situation... I would probably use the trigger. It looks like you have about 2 or 3 different tables you need in your cross-tab... Which is pretty performance intensive. Do Something like this...

Add the fields
Colors_Static,Sizes_Static to your product table. Add triggers to the Color and size connector tables. The trigger is executed on insert and delete. When those are fired place a comma seperated list 'red,green,blue' in colors_static and sizes_static and any others that you need like that. This way you can benefit from the denormalized data and the normalized data and you only have to do the looping on inserts and deletes on your connecter tables. As long as colors_static and sizes_static arent manually updated ( you shouldnt have to ) then everything is cool.

You may be wondering.. doesnt that defeat the purpose of normalizing it? Well.. normalization is more then just saving space... Its about flexibility too.. you can add more colors and more sizes to your application... do searches by size id and color id from a pulldown. Static data is never a good idea from my experience.. But in this situation... as long as your static data piggy backs off the triggers... you will gain performance and flexibility.

MrKicks
Starting Member

6 Posts

Posted - 2004-10-03 : 02:31:47

Thank you for the suggestions, I am off to see the wizard about Dynamic cross tabs and triggers although it sounds like triggers will come first!

what are the Pros or Cons about querying this from asp and parsing through the results there vs. using the triggers or Dynamic cross tabs? Or is this not the place for that kinda thing?

Thanx again,
MrKicks

slacker
Posting Yak Master

115 Posts

Posted - 2004-10-03 : 03:03:59

quote:
Originally posted by MrKicks

what are the Pros or Cons about querying this from asp and parsing through the results there vs. using the triggers or Dynamic cross tabs? Or is this not the place for that kinda thing?

Thanx again,
MrKicks

Well consider how you would parse it in asp... you would either

A. For each record in your list retrieve a recordset of color, size and whatever else.. Thats no good imho.. that means 2 db hits per record. It would work fine for a single product. But not a list of products where you would have to display sizes and colors. In asp you want to minimize the amount of queries you make.

B. Retrieve your product list.. and do one query which retrieved all
color and size information into one result set. Then you would create some function like GetColors and GetSizes that would have
productid as a parameter that would search through the recordset
to grab sizes or colors for the product on a given row.

B would be preferred if parsing in asp. Are you using asp.net? or classic asp. If asp.net there are a number of things you can do to improve this such as page caching or even just caching a dataset.

So the pro's of that is really just allowing you to keep your queries in your db simple. No cross tabs.. no looping in the db. Allow the programming in asp to handle that logic which its better at.

The cons... messier code.. more db connections. If you had a cross tab you can open your connection get your results and be done with in one query. Thats the way I would prefer to do it. Less db connections is good. But you dont want to have a query that's too slow either. If your only looking at a few hundred products... see if you can get the cross tab working which really shouldnt be too intensive.. And optimal would be to just have the static fields with the comma list... Then its just one normal query and easy parse on asp. And I imagine once you had a product list set you wouldn't need to worry about the triggers executing all the time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources