List:General Discussion« Previous MessageNext Message »
From:Jules Bean Date:May 10 1999 11:01pm
Subject:Re: syntax/sql question
View as plain text  
sam1600@stripped wrote:
> 
> sam1600@stripped wrote:
> Here is what a single order(23) may look like:
> 
> custorder:
> +----------+------------+
> | order_id | package_id |
> +----------+------------+
> |       23 |         23 |
> +----------+------------+
> 
> product_package:
> +------------+------------+
> | package_id | product_id |
> +------------+------------+
> |         23 |          1 |
> |         23 |          4 |
> +------------+------------+
> 


Why not simply this?


> +------------+------------+
> |   order_id | product_id |
> +------------+------------+
> |         23 |          1 |
> |         23 |          4 |
> +------------+------------+


I.e. the order table has multiple rows, one for each product in the
order.  (I'd avoid the term package - as sasha indicates, a package
suggests a grouping which is reused.  You're talking about 1-time
groupings).

Answering my own questions, here are two possible reasons why not:

1) The orders table has loads of other data which is now duplicated.

Solution:

Order becomes - (order_id,customer_id,lots_of_other_order_info)
New table:

order_contents = (order_id,product_id)

order_contents has multiple lines per order, one for each product.

2) My customers need to order more that one of some items!

Solution:

Add a 'how_many' field to order_contents.

Hope that helps,

Jules
-- 
/----------------+-------------------------------+---------------------\
|  Jelibean aka  | jules@stripped         |  6 Evelyn Rd        |
|  Jules aka     |                               |  Richmond, Surrey   |
|  Julian Bean   | jmlb2@stripped        |  TW9 2TF *UK*       |
+----------------+-------------------------------+---------------------+
|  War doesn't demonstrate who's right... just who's left.             |
|  When privacy is outlawed... only the outlaws have privacy.          |
\----------------------------------------------------------------------/
Thread
syntax/sql questionsam16008 May
  • Re: syntax/sql questionSasha Pachev8 May
  • Re: syntax/sql questionMike Machado8 May
Re: syntax/sql questionsam16008 May
  • Re: syntax/sql questionSasha Pachev8 May
  • Re: syntax/sql questionJules Bean11 May