[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