Re: Upgrade from 5.6 => 5.7

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



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.


_______________________________________________
CentOS mailing list
CentOS@xxxxxxxxxx
http://lists.centos.org/mailman/listinfo/centos


[Index of Archives]     [CentOS]     [CentOS Announce]     [CentOS Development]     [CentOS ARM Devel]     [CentOS Docs]     [CentOS Virtualization]     [Carrier Grade Linux]     [Linux Media]     [Asterisk]     [DCCP]     [Netdev]     [Xorg]     [Linux USB]
  Powered by Linux