Multiple Product tables 4

[JoeAtWork]

I'm currently starting a rewrite of an inventory system. One of the challenges is that about 50% of the products are of a particular category, that has many properties that don't apply to the other products. Some example properties that only apply to the first 50% are Width, Height, Die Lot, IsGlazed, etc.

One of the other programmers has proposed that we make one main Products table that has all fields that are common to all types of products. Then we make a secondary Products table that has these extra fields that only apply to this special category. The relation between the main and secondary table would be one-to-(zero-or-one).

The goal here is to not have a bunch of fields that don't apply to 50% of the products.

What are your opinions or alternatives to this solution?

 

[AlexCuse]

I've used a similar setup before, where the relationship was either one to one or one to many. It worked out pretty well for me.

I'd just be sure to have a view ready to go that works as a surrogate products table, so you don't have to worry about the joins between the two tables in other spots. The best code is the code you don't have to write

Good Luck,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[foxdev]

I'd lean towards having only one Products table, with a child table for the extra attributes that apply to certain products (with a FK to Products, of course).

That would meet your goal of efficiency, and still have all your product master list in one table.

--------------

http://www.foxdev.com

 

[JoeAtWork]

Thanks Alex and FoxDev.

Anyone want to make a tie-breaking vote?

 

[gmmastros]

I think Alex and foxdev are saying the same thing. Use multiple tables.

When you do this, it is called vertical partitioning. Some people will do this simply because it improves performance. The idea is... separate your data in to logical partitions (where the data in each table is likely to be queried at the same time).

For example.... products

There may be several columns related to price/quantities available. Other columns to describe the product (length, width, height, weight, color, etc...). You may also have other columns that are accessed infrequently, so separating them in to their own table my be advantageous, too. For example, suppose you have large varchar columns with an advertisement/description of the product. You may want to include this in a table all by itself.

Ordinarily, you would think that making many tables (and therefore needing to join them together) would be slower than having one big table. It's weird, but with SQL Server, this is not the case. Having many tables is beneficial particularly when you don't need much of the information from some of the tables. This works because SQL Server stores data in 8K pages. The more records you can fit in to a page, the less I/O you will have, which results in better performance.

I'm not suggesting you get too carried away, because that can hurt you too. You wouldn't want a table for product_weight, product_height, product_width, etc.... What I am suggesting is that you examine your current queries and separate the data in to logical groups and then make tables from those groups.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AlexCuse]

Wait a minute, I thought what I was voting for was the same as what FoxDev suggests?

something like:

main product table:

prod propertynormal
1 asdf
2 xxva

secondary product table:
prod propertyextended
2 asdfasdfagqwetq

I've done the same thing, but with a one to (zero|one|many) relationship, was what I meant above. With one to zero|one I'd think it becomes much easier, as you can use a "real" key rather than a composite key. on the secondary table.

Do you still need a tiebreaker then?

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[AlexCuse]

As usual, George says it much more quickly and eloquently ;-)

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[MrPlough69]

It sounds like a possible candidate for table inheritance as described here:

http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server

 

[JoeAtWork]

No tie-breaker needed, just a new pair of reading glasses apparently.

You have all been a great help.

So it looks like I will be going with the "inherited tables" approach.