[thelist] sql/table design help
rudy
r937 at interlog.com
Thu Nov 21 08:20:01 CST 2002
> 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
that would work but it would be needlessly complex
a price table, with entries like $2.98, $7.98, etc., would have what as its
primary key? the first solution that comes to mind is the price itself, and
then you'd have a table with one column, and -- here comes the kicker -- you
would never need to join to it because the foreign key in whatever table
references the price table carries the value!
anyhow, the thing you want to split out, to normalize, is the size
thus, price depends on the item's size
create table items
( itemid integer primary key
, itemname varchar(100) not null
)
create table itemsizes
( itemid integer
, itemsize char(10)
, itemprice numeric(7,2)
, primary key ( itemid, itemsize)
, foreign key (itemid) references items
)
note that the primary key of itemsize is composite,
and that one of the primary key columns is a foreign key
many people would declare a surrogate primary key for the itemsizes table
(e.g. itemsizesid), but this is superfluous, unnecessary, and inefficient
to get all the prices for each item,
select itemname, itemsize, itemprice
from items
inner join itemsizes
on items.itemid = itemsizes.itemid
order by itemname, itemsize
this will bring back rows that look like this --
t-shirt S 9.98
t-shirt M 10.98
t-shirt L 11.98
t-shirt XL 12.98
shoes 7 40.00
shoes 8 40.00
shoes 9 40.00
shoes 10 40.00
shoes 11 40.00
shoes 12 40.00
necktie - 29.98
if you want to "suppress" repeated item names after the first in each group,
you will have to do that with your scripting or reporting language (the
"looping" that anthony referred to)
the shoes and necktie examples above illustrate that there might actually be
a deeper problem in your data design, in that some items have the same price
for all sizes, and some items don't have a size, and yet the price is
obtained only by a join to the itemsizes table -- but these considerations
are probably too involved for you at this point
rudy
More information about the thelist
mailing list