From: JanWieck@t-online.de
To: Brett W. McCoy <bmccoy@chapelperilous.net>
Cc: John <john@akadine.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: confused by select.
Date: Thu, 6 Jul 2000 23:31:44 +0200 (MEST)
Message-ID: <200007062131.XAA19912@hot.jw.home> (raw)
In-Reply-To: <Pine.LNX.4.10.10007061623300.13683-100000@chapelperilous.net>
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 #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: JanWieck@t-online.de, bmccoy@chapelperilous.net, john@akadine.com
Subject: Re: confused by select.
In-Reply-To: <200007062131.XAA19912@hot.jw.home>
References: <Pine.LNX.4.10.10007061623300.13683-100000@chapelperilous.net> <200007062131.XAA19912@hot.jw.home>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox