pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
confused by select.
7+ messages / 6 participants
[nested] [flat]

* confused by select.
@ 2000-07-06 20:17  John <john@akadine.com>
  0 siblings, 3 replies; 7+ messages in thread

From: John @ 2000-07-06 20:17 UTC (permalink / raw)
  To: pgsql-sql

Hello.  I'm trying to do a select here that i have looked at from many
angles and cannot find a solution too.  My main problem, (i believe) is
that it is trying to create a many to many relationship.  I would be
grateful if anyone knew a way around this.

Here's my predicamint.
I have a database for sales orders.
An inventory table.
And
A history table.

Inventory:
    Create t1 (sku char(4), type char(1));
History:
    Create t2 (id char(6), items text);

[There are more fields, but this is all that matters for this query]

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>
Problem B: there are many skus of each type.
           as are there many purchases.

What would the proper select be?

create view v1 (select sku from t1 where type ='K');
       will get me all the skus of one type but i don't know where to go
from there. And it feels as if i've exhausted all options.

i've been working around:
    select id from t2 where items like sku;
and no matter what i use in the where clause (regex, like, or wildcards).
i get back an error or a zero.
and there are no other related fields in the mentioned tables.

is there a way to step through the sku field item by item without leaving
postgres (i.e. resorting to scripting)?

I have also tried different fieldtypes for the 'items' field.
But they all give me problems too.
The array works much the same way as the : delimitted field i have does.
Except you have less operators that work with it.
And to break it up into separate items fields. (item1, item2, item3,
etc.) is a waste, seeing as the average order is 2.? but there are many
orders with hundreds of items.


Sorry for the long winded explanation.
But I figured, that the more imformation i gave, the more someone may be
able to help.

Thanks in advance.
.jtp




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: confused by select.
@ 2000-07-06 20:38  Brett W. McCoy <bmccoy@chapelperilous.net>
  parent: John <john@akadine.com>
  2 siblings, 1 reply; 7+ messages in thread

From: Brett W. McCoy @ 2000-07-06 20:38 UTC (permalink / raw)
  To: John <john@akadine.com>; +Cc: pgsql-sql

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.

Brett W. McCoy                        
                                              http://www.chapelperilous.net
---------------------------------------------------------------------------
Virtue does not always demand a heavy sacrifice -- only the willingness
to make it when necessary.
		-- Frederick Dunn









^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: confused by select.
@ 2000-07-06 21:05  Richard <blackw@sfu.ca>
  parent: John <john@akadine.com>
  2 siblings, 0 replies; 7+ messages in thread

From: Richard @ 2000-07-06 21:05 UTC (permalink / raw)
  To: ; +Cc: pgsql-sql

John wrote:
> 
> Hello.  I'm trying to do a select here that i have looked at from many
> angles and cannot find a solution too.  My main problem, (i believe) is
> that it is trying to create a many to many relationship.  I would be
> grateful if anyone knew a way around this.
> 

I believe that you would find your task to be MUCH simpler if you
normalize your HISTORY (t2) table.  That is, don't lump all the SKUs
from one purchase in a single record.

You could normalize this table a bit:

     create history (id char(6), purchase_order char(6), sku char(4));

	e.g.:     id     purchase_order    sku
                ----------------------------------
                 4001       A55321         1111
                 4001       A55321         1212
                 4001       A55321         W233
                 (...)


      select distinct id form history where sku in (select sku from t1
where type='W');

Or you could even normalize it further:


     create sale (id char(6), purchase_order char(6));
     create history (purchase_order char(6), sku char(4));

      select distinct id
           from sales s, history h
           where s.purchase_order = h.purchase_order
             and sku in (select sku from t1 where type='W');

You will find at least the following two things are true if you
normalize this data:

1. The query you are trying to pose will become simple.
2. Queries involving the SKU number in your HISTORY table will be solved
for much quicker.


Cheers,
Richard



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: confused by select.
@ 2000-07-06 21:31  Jan Wieck <JanWieck@t-online.de>
  parent: Brett W. McCoy <bmccoy@chapelperilous.net>
  0 siblings, 1 reply; 7+ messages in thread

From: JanWieck@t-online.de @ 2000-07-06 21:31 UTC (permalink / raw)
  To: Brett W. McCoy <bmccoy@chapelperilous.net>; +Cc: John <john@akadine.com>; pgsql-sql

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 #





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: confused by select.
@ 2000-07-07 01:12  Brett W. McCoy <bmccoy@chapelperilous.net>
  parent: Jan Wieck <JanWieck@t-online.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Brett W. McCoy @ 2000-07-07 01:12 UTC (permalink / raw)
  To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: John <john@akadine.com>; pgsql-sql

On Thu, 6 Jul 2000, Jan Wieck wrote:

>     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.

I was going to say that, but assumed it would be obvious. :-)

Brett W. McCoy                        
                                              http://www.chapelperilous.net
---------------------------------------------------------------------------
Conscience is the inner voice that warns us somebody may be looking.
		-- H.L. Mencken, "A Mencken Chrestomathy"




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: confused by select.
@ 2000-07-07 09:44  Patrick Jacquot <patrick.jacquot@anpe.fr>
  parent: John <john@akadine.com>
  2 siblings, 1 reply; 7+ messages in thread

From: Patrick Jacquot @ 2000-07-07 09:44 UTC (permalink / raw)
  To: John <john@akadine.com>; +Cc: pgsql-sql

John wrote:

> Hello.  I'm trying to do a select here that i have looked at from many
> angles and cannot find a solution too.  My main problem, (i believe) is
> that it is trying to create a many to many relationship.  I would be
> grateful if anyone knew a way around this.
>
> Here's my predicamint.
> I have a database for sales orders.
> An inventory table.
> And
> A history table.
>
> Inventory:
>     Create t1 (sku char(4), type char(1));
> History:
>     Create t2 (id char(6), items text);
>
> [There are more fields, but this is all that matters for this query]
>
> 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>
> Problem B: there are many skus of each type.
>            as are there many purchases.
>
> What would the proper select be?
>
> create view v1 (select sku from t1 where type ='K');
>        will get me all the skus of one type but i don't know where to go
> from there. And it feels as if i've exhausted all options.
>
> i've been working around:
>     select id from t2 where items like sku;
> and no matter what i use in the where clause (regex, like, or wildcards).
> i get back an error or a zero.
> and there are no other related fields in the mentioned tables.
>
> is there a way to step through the sku field item by item without leaving
> postgres (i.e. resorting to scripting)?
>
> I have also tried different fieldtypes for the 'items' field.
> But they all give me problems too.
> The array works much the same way as the : delimitted field i have does.
> Except you have less operators that work with it.
> And to break it up into separate items fields. (item1, item2, item3,
> etc.) is a waste, seeing as the average order is 2.? but there are many
> orders with hundreds of items.
>
> Sorry for the long winded explanation.
> But I figured, that the more imformation i gave, the more someone may be
> able to help.
>
> Thanks in advance.
> .jtp

usually many-to-many relationships are handled by a third table, like this:

create table items (item_id,...)
create table customers (customer_id, ...)
create table orders (customer_id, item_id, quantity_orderered)

Hoping it may help

Patrick JACQUOT




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: confused by select.
@ 2000-07-07 19:32  Antoine Reid <fluke@kernel-panic.net>
  parent: Patrick Jacquot <patrick.jacquot@anpe.fr>
  0 siblings, 0 replies; 7+ messages in thread

From: Antoine Reid @ 2000-07-07 19:32 UTC (permalink / raw)
  To: Patrick Jacquot <patrick.jacquot@anpe.fr>; +Cc: John <john@akadine.com>; pgsql-sql

On Fri, Jul 07, 2000 at 11:44:30AM +0200, Patrick Jacquot wrote:
[snip]
> 
> usually many-to-many relationships are handled by a third table, like this:
> 
> create table items (item_id,...)
> create table customers (customer_id, ...)
> create table orders (customer_id, item_id, quantity_orderered)

Hi!  In real life situations, I can only see one possible problem with that.
Let's say you have a product in stock, for a long period of time, and at some
point, you need to change the description of the product... Now what happens
when you look at your old invoices?  They show up with the new description..

What if you need to delete a product? :)
What if the shipping address changes?

I have found, as a requirement in many of my projects, that we be able to view 
invoices/orders /exactly/ like they were..  It involves a different set of
tables, one for the 'open' orders, inventory, etc and another set of tables, 
that are populated for every invoice. (Yes, that means lots of duplication..)

> 
> Hoping it may help
> 
> Patrick JACQUOT
> 
just my 1/50$ CDN
(for what it's worth nowadays...)

Antoine Reid





^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2000-07-07 19:32 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-06 20:17 confused by select. John <john@akadine.com>
2000-07-06 20:38 ` Brett W. McCoy <bmccoy@chapelperilous.net>
2000-07-06 21:31   ` Jan Wieck <JanWieck@t-online.de>
2000-07-07 01:12     ` Brett W. McCoy <bmccoy@chapelperilous.net>
2000-07-06 21:05 ` Richard <blackw@sfu.ca>
2000-07-07 09:44 ` Patrick Jacquot <patrick.jacquot@anpe.fr>
2000-07-07 19:32   ` Antoine Reid <fluke@kernel-panic.net>

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