Received: from mailout01.sul.t-online.com (mailout01.sul.t-online.com [194.25.134.80]) by hub.org (8.10.1/8.10.1) with ESMTP id e66Lu3W15949 for ; Thu, 6 Jul 2000 17:56:03 -0400 (EDT) Received: from fwd02.sul.t-online.com by mailout01.sul.t-online.com with smtp id 13AJco-0002qL-00; Thu, 6 Jul 2000 23:56:02 +0200 Received: from hot.jw.home (340000654369-0001@[193.159.121.250]) by fwd02.sul.t-online.com with esmtp id 13AJcb-1qyuvIC; Thu, 6 Jul 2000 23:55:49 +0200 Received: (from wieck@localhost) by hot.jw.home (8.8.5/8.8.5) id XAA19912; Thu, 6 Jul 2000 23:31:45 +0200 From: JanWieck@t-online.de (Jan Wieck) Message-Id: <200007062131.XAA19912@hot.jw.home> Subject: Re: confused by select. In-Reply-To: from "Brett W. McCoy" at "Jul 6, 2000 04:38:49 pm" To: "Brett W. McCoy" Date: Thu, 6 Jul 2000 23:31:44 +0200 (MEST) CC: John , pgsql-sql@postgresql.org Reply-To: Jan Wieck X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Sender: 340000654369-0001@t-dialin.net X-Archive-Number: 200007/67 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. > > > > 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 #