[thelist] sql/table design help
Tab Alleman
Tab.Alleman at MetroGuide.com
Thu Nov 21 09:37:01 CST 2002
Every time I've worked with hard goods catalogs, every possible
variation of an item has had a unique SKU that made a perfect primary
key. So if you sell foos that come in different sizes, and bars that
are one size fits all, here's what my table might look like:
SKU Category Price Long Description
FOO-L FOO $3 Foo, Large
FOO-M FOO $2 Foo, Medium
FOO-S FOO $1 Foo, Small
BAR BAR $1,000 Bar
Then you've got the category column (which I would really cross-index
with a category types table) for grouping purposes.
I hope Rudy doesn't rip apart my solution. : )
-T
-----Original Message-----
From: Tom Dell'Aringa [mailto:pixelmech at yahoo.com]
Yes, me again with more questions on sql and my table design. Here's the
trouble: I have a table that has items. These items sometimes have more
than one size, therefore more than one price. When I list them, I only
want to list the item once, and then all the prices.
Right now I have a item table like this:
+--------+------+-------+
| itemID | item | price |
+--------+------+-------+
So first question: It seems then I will have a one to many relationship
so I should break out the price into another table, probably an
intersection table? Like thus
itemID | priceID
Is this the best way? 2nd q: how to i write the query to get the item
name once, but all the prices? Not sure where to start with that one...
More information about the thelist
mailing list