473,241 Members | 1,530 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

SCHEMA -- 1000 products with different attributes

[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!
Jul 20 '05 #1
4 2880
> 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.
Jul 20 '05 #2
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
Jul 20 '05 #3
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.
Jul 20 '05 #4
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
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Pieter | last post by:
I've read a lot of posts on "why relax ng is so very good" and on "why w3c xml schema should be the only schema language". I'm, however, still not clear on why I should prefer one over the other. ...
5
by: Ralf Wahner | last post by:
Dear Masters of XML As I'm new to XML Schema I dare to ask a possibly recurring question: Given an element <elem> with two attributes @a and @b. The attributes are bound by the condition, that...
2
by: Shailendra Batham | last post by:
Hello Gurus, I want to put some restrictions on my attribute tag in my XML Schema, anyone out there have any idea how to do that. here is my XML and the XML Schema <?xml version="1.0"...
6
by: Martin | last post by:
Hi, I have a xml file like the one below <?xml version="1.0" encoding="utf-8"?><e1 xmlns:e1="http://tempuri.org/Source1.xsd" e1:att1="1" e1:att2="2" e1:rest="345"/> If I try to create a...
2
by: Chuck Bowling | last post by:
AIML Schema: http://209.168.21.76/CommunityStarterKit/Downloads/258.aspx I have a Schema (in the link above) that I've been trying to make work in VS2003 for a while now and just can't seem to...
9
by: Terry E Dow | last post by:
Howdy, I am having trouble with the objectCategory=group member.Count attribute. I get one of three counts, a number between 1-999, no member (does not contain member property), or 0. Using...
5
by: paul_zaoldyeck | last post by:
does anyone know how to validate an xml file against multiple defined schema? can you show me some examples? i'm making here an xml reader.. thank you
22
by: klenwell | last post by:
I'm in the process of refactoring the php code base I've amassed over the last few years into an object-oriented framework. I'm about to start in on the authentication/login extension and I've...
3
by: hammie | last post by:
I'm utterless clueless on how I can go about declaring in my schema that two attributes must contain different values, e.g. <mail to="" from="">Text</mail> How do I set the restriction in the...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.