On Thu, Sep 15, 2011 at 08:51:23PM +0100, Always Learning wrote:
>
> 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 :-)
I think this is how we all started learning SQL and writing web
applications... without normalization. And it won't cause you much
grief in simpler use case scenarios with smaller data sizes.
You might take a stab at learning normalization though. It's really
quite intuitive, helps keep your tables from "column bloat" and you can
offload a lot of the processing to the SQL engine instead of passing
unnecessary information from the DB to your app layer and doing
processing there. It also forces you to put a little more thought into
design and you'll end up with a schema another DBA could look at and
not run away scared. :)
My $0.02 anyways!
Ray