On 09/15/11 12:51 PM, Always Learning wrote:
>> 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.
once again, you completely miss the point, and go off on a tangent
explaining how YOU do things, justifying your ignorance.
ignoring the fact that I contrived my example in a few minutes, and it
was not intended to be a realistic complete implementation of a order
processing system, that same set of normalized tables could be used to
generate a result like...
select c.name, c.address, 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)
group by c.id;
which would output a list of all customers with their name, address, and total of their sales.
select cati.description, sum(oi.qty) from orderlineitem oi
join catalogitem cati on (cati.id=oi.catalogid)
group by cati.id order by sum(oi.qty) desc;
would output a list of all catalog items and the total quantity. a slightly more involved query could summarize this by month or by year by customer, etc etc.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast