[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