Re: confused by select.

Brett W. McCoy wrote:
> On Thu, 6 Jul 2000, John wrote:
>
> > I would like to get the id's where the customer has purchased an item of a
> > specific type.
> >
> > Problem A: most people order more than one item at a time.
> > So the 'items' field is a colon delimitted text field containing the
> > skus of the purchased items.
> > <example of items field -- 1111:1212:W233:QA66>
>
> I don't understand why you are doing it this way? Why not create a
> history table with individual skus that are each part of an order?
>
> create table history (id int2, order int2, sku char(4));
>
> You would, of course, put some constraints to make sure that skus in the
> history table actually exist in the inventory table (i.e., foreign key),
> and have the history id as a serial type to make the primary key. Then you
> can have the same order number reference multiple inventory items.
>
> Then you can do easier joins, search for unique orders with a count of
> items in each order, and so forth, all in SQL.

IMHO the correct suggestion. Just want to underline it.

A list of purchases is usually a subset of another relation.
Remember, RDBMS means RELATIONAL Database Management System!
So if you setup your tables with a relational angle of view,
the system will do well.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #