On Thu, 2011-09-15 at 10:42 -0700, John R Pierce wrote:
> lets come up with a really simplistic example here.
>
> table: customers{id, name, address}
> table: catalogitem(id,description,price}
> table: customerorder{id,customer references customers(id),date}
> table: orderlineitem{orderid references customerorder(id),catalogid
> references catalogitem(id), qty}
>
> that data is normalized, there is no redundant data in any of those
> tables, they are connected by the relations defined via the references
> ('foreign keys').
I would not design my orders database exactly like you have.
If I knew the system user wanted to know "how much customer named 'joe'
has ordered in 2010," then I would first ask
? by value
? by quantity of different items
? by gross quantity of all items
I might even make a table like this:-
C1
c1ref
c1customer (code)
c1quantity (integers only)
c1price (in cents)
c1discount (2 decimal places held as integers)
c1catalogue (code)
c1date (yymmdd)
c1order (number)
c1comments (text)
then do a query:
select c1quantity, c1price, c1discount from c1 where c1customer =
'joebloggs' and c1date like '10%'
while ...
$value.= ($c1price*$c1quantity*((100-$c1discount)/100));
> now, if we want to pull up a summary of how much customer named 'joe'
> has ordered in 2010, we'd do something like...
>
> select sum(ci.price*oi.qty) from customers c
> join customerorders co on (co.customer=c.id)
> join orderlineitem oi on (co.id=oi.catalogid)
> join catalogitem cati on (cati.id=oi.catalogid)
> where c.name = 'joe' and extract (year from co.date) = 2010;
Never used SQL sum, so I would try
select sum($c1price*$c1quantity*((100-$c1discount)/100)) from c1 where
c1customer = 'joebloggs' and c1date like '10%'
Not a 'join' insight :-)
--
With best regards,
Paul.
England,
EU.