[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! 4 2896
> 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.
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
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 "StockToHan d" 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.
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 "StockToHan d" 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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.
I've made a small list of some good and bad points of both. These
points don't really go into the grammar aspects of these languages,
but are more...
|
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 either both
or none must be present, i.e.
<elem a="val_a" b="val_b"/> or <elem/>
is valid, whilst
|
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" encoding="utf-8"?>
<Book ID="1000000000">
<Name>XML Basic</Name>
<Comments>Whatever</Comments>
|
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 schema for it with Visual Studio, I get the error
"Failed to create a schema for this data file because:
|
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 get right. I didn't design the
schema and to tell the truth, most of it is beyond my rudimentary
understanding of XML.
So far, the main problem...
| |
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 LDIFDE as a comparison I get the same
results. No members means just that, an empty group. Zero means that the
DirectorySearcher.SizeLimit has been...
|
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
|
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 been thinking
about different approaches to the mysql table schema that stores basic
user login information. At present, user authentication is keyed...
|
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 schema that the "to" attribute and "from" attribute must contain different values? Hence 'to="Amy" from="Bob"' will validate, while 'to="Amy"...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |