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 :-)