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@Yahoo.com #