On 09/14/11 8:36 PM, Always Learning wrote:
>> > And, if you've never used a SQL join, you don't know the first thing
>> > about*relational* databases, you've been using SQL as though it was a
>> > simple flat table ISAM, DBase-style circa 1983. Might as well use
>> > BerkeleyDB for that, its even faster and lighter weight.
> Golly. I grew-up in real computers. Relational databases are simply
> database structures, linking records. There is no reason to use joins
> and views IF the database is carefully planned. Joins and views are
> another overhead. Rule Number 000001 in programming is Keep It Simple.
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').
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;
boom. one query. concise. one round trip to the database engine, the
database engine does all the heavy lifting, for which its designed, and
it returns just the data you need to answer this query.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast