SCHEMA -- 1000 products with different attributes

[Adie]

[crossposted]

Hi, I wonder if anyone might lend me a brain.

I have a stock database to build that covers over 1000 products, which
might be said to exist in around 50 product families.

Obviously, just to be awkward all the types of stock will have
different attributes. So one product might be a tube with
inside/outside diameter and length and another a T shaped cable joint.

All I can come up with is a separate table for each stock type family
and store the table name and product code in the main stock table, so:

Tables:
ProdA ProdB ProdC Stock

Stock attributes:
ProdId
ProdTable
Amount
Date
etc..

ProdA attribute:
ProdId
AttributeX
AttributeY
AttributeZ
etc..

Then use code to parse the table and product ID to select the correct
query to get the product details. BUT This seems awefuly inelegant and
potentially wrong so I'm loathe to continue down this route.

Can anyone tell me the "right" way to do this, I feel sure it must be
a classic db design exercise, but unfortunatly one they didn't teach
us at University -- or maybe I was asleep...

Thanks!

[louis nguyen]

> Tables:

ProdA ProdB ProdC Stock

Stock attributes:
ProdId
ProdTable
Amount
Date
etc..

ProdA attribute:
ProdId
AttributeX
AttributeY
AttributeZ
etc..

Then use code to parse the table and product ID to select the correct
query to get the product details. BUT This seems awefuly inelegant and
potentially wrong so I'm loathe to continue down this route.

I would go with your original thought and create separate tables for
the 50 product families. For reporting, I would create
summarized/aggregate tables off the OLTP. On the other hand, if the
primary business requirement asks for something crazy like, get the
average of attributeX across all product families (if the product
family has attributeX) -- then that's another matter.

[Erland Sommarskog]

Adie (ar*******@h-o-t-m-a-i-l.com) writes:

I have a stock database to build that covers over 1000 products, which
might be said to exist in around 50 product families.

Obviously, just to be awkward all the types of stock will have
different attributes. So one product might be a tube with
inside/outside diameter and length and another a T shaped cable joint.

All I can come up with is a separate table for each stock type family
and store the table name and product code in the main stock table, so:

Tables:
ProdA ProdB ProdC Stock

Stock attributes:
ProdId
ProdTable
Amount
Date
etc..

ProdA attribute:
ProdId
AttributeX
AttributeY
AttributeZ
etc..

Then use code to parse the table and product ID to select the correct
query to get the product details. BUT This seems awefuly inelegant and
potentially wrong so I'm loathe to continue down this route.

That would seem like a fairly normal way of doing it.

Except that putting the table name in the main table is maybe not
that elegant. Rather you have just have a code which gives you the
product category, and then you know that category B is in table
tubeproducts. But that's a subtle difference.

The other alternative is to have a subtable:

CREATE TABLE stockattributes (prodid int NOT NULL,
attrname varchar(50) NOT NULL,
attrvalue sql_variant NOT NULL,
CONSTRAINT pk_attr PRIMARY KEY (prodid, attrname))

This gives you less tables, but if you misspel an attribute name,
the compiler will not catch it. If the attributes are just for
listing, this might be OK, but there is a lot of logic around
each attribute, then this is not really funny.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Adie]

Erland Sommarskog wrote:

Adie (ar*******@h-o-t-m-a-i-l.com) writes:

I have a stock database to build that covers over 1000 products, which
might be said to exist in around 50 product families.

Obviously, just to be awkward all the types of stock will have
different attributes. So one product might be a tube with
inside/outside diameter and length and another a T shaped cable joint.

All I can come up with is a separate table for each stock type family
and store the table name and product code in the main stock table, so:

Tables:
ProdA ProdB ProdC Stock

Stock attributes:
ProdId
ProdTable
Amount
Date
etc..

ProdA attribute:
ProdId
AttributeX
AttributeY
AttributeZ
etc..

Then use code to parse the table and product ID to select the correct
query to get the product details. BUT This seems awefuly inelegant and
potentially wrong so I'm loathe to continue down this route.
That would seem like a fairly normal way of doing it.

Except that putting the table name in the main table is maybe not
that elegant. Rather you have just have a code which gives you the
product category, and then you know that category B is in table
tubeproducts. But that's a subtle difference.

One thing that narcs me about going down this route is that whenever a
new product is added I will have to create a new table *and* write a
new class to handle the implementation in the application. It also
means I cant get the product details from the main "StockToHand" table
with just sql, I'll always be reliant on code - I think.

Let me just try and get this straight, would put the actual table name
in the table?

If so, would this query work?

SELECT ProductDesc
From (
SELECT ProdTable
FROM StockToHand
WHERE Batch = 999
AND ProdID = 1
)
WHERE ProdID = 1;

Is that what you mean? Sorry I should prototype this myself, but would
like to hear what else you have to say.

I was thinking about another route, whereby I just have one table with
all the possible attributes - that could potentially mean 100 columns
in the products table -- but I can just let my OO Classes take care of
which attributes to pull from the table with SQL. Benefits of this
route, less tables,and not adding new tables, I only have to keep
adding new classes. I guess the table will be pretty big and have a
lot of empty fields though.
The other alternative is to have a subtable:

CREATE TABLE stockattributes (prodid int NOT NULL,
attrname varchar(50) NOT NULL,
attrvalue sql_variant NOT NULL,
CONSTRAINT pk_attr PRIMARY KEY (prodid, attrname))

This gives you less tables, but if you misspel an attribute name,
the compiler will not catch it. If the attributes are just for
listing, this might be OK, but there is a lot of logic around
each attribute, then this is not really funny.

I'll have to think about that one a bit.

[Erland Sommarskog]

Adie (ar*******@h-o-t-m-a-i-l.com) writes:

One thing that narcs me about going down this route is that whenever a
new product is added I will have to create a new table *and* write a
new class to handle the implementation in the application. It also
means I cant get the product details from the main "StockToHand" table
with just sql, I'll always be reliant on code - I think.
That depends a little on how often you expect to add new product families.
If you need one table for each product, this does not sound like a good
idea. And even by family, it's getting a bit too much if you have 50
already.

I have something similar in the system I work with, but here the grouping
is for financial instruments, and adding support for new type of instruments
is a major development point. We have something like 13 types today.
Let me just try and get this straight, would put the actual table name
in the table?

If so, would this query work?

SELECT ProductDesc
From (
SELECT ProdTable
FROM StockToHand
WHERE Batch = 999
AND ProdID = 1
)
WHERE ProdID = 1;
No, that would not work. You would need to use dynamic SQL, or have
IF statements leading you to the right table.

The above syntax is, by the way, not too far from a correct SQL query.
This is a version that will run:

SELECT ProductDesc
From (
SELECT ProdTable, ProdID
FROM StockToHand
WHERE Batch = 999
AND ProdID = 1
) as x
WHERE ProdID = 1;

This is a derived table. It's not going to help you solve your problem,
but I mention it, because it's a very useful feature in general.
I was thinking about another route, whereby I just have one table with
all the possible attributes - that could potentially mean 100 columns
in the products table -- but I can just let my OO Classes take care of
which attributes to pull from the table with SQL. Benefits of this
route, less tables,and not adding new tables, I only have to keep
adding new classes. I guess the table will be pretty big and have a
lot of empty fields though.

I have a feeling that whichever way you go, it's going to be something
which has it drawbacks. Even with one single wide table, there will
always come a new product with a new attribute that you don't have.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp