Re: counting query

>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of John Meyer
>Sent: zondag 28 januari 2007 15:36
>To: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] counting query
>
>Joris Dobbelsteen wrote:
>>>
>>> CREATE TABLE attendance
>>> (
>>>  attendanceid serial primary key,
>>
>> Why you have this??? You already have (entered,timeperiod,studentid)
>> that you can use, since that must be unique too. Try to avoid
>> surrogate keys as much as possible (it really increases performance
>> and ease-of-writing for complex queries!
>
>
>Correct me if I am wrong, but wouldn't a simple number be a
>lot easier to look up than a composite key?

No, it is not.
Better, it is, It might be, until you go just a bit larger.

I've learned it when I've build a production database (that's still used
in production and still performs excellent. At the time I was only 15 or
16 or 17 years old. That was 7 to 10 years ago. Since then I've learned
a lot.)

The trouble is, the database consists of well over 40 tables (with
nearly surrogate keys) and joining a bit of data on the far ends of the
database requires you to join arround 10 to 15 tables. If you are doing
something complex you will get lost at some point and really need
graphical tools to just grasp what you are trying to query.
So a bit of a complex query easily results in a excessive number of
tables that must be queried.

Why I did this. At this time I was not aware that you could build a
primary key consisting of multiple columns. And if you look arround you
at the Internet you see (nearly) all databases of free 'web
applications' making excessive use of surrogate keys.
This resulted in a lot of excessive surrogate keys that could have been
easily avoided and where not a required atrifact of the inconsistent
data I had to import.

What would have been better without surrogate keys all-over:
* Easier to write complex queries with much fewer tables to be queried.
* Much faster query performance, as fewer tables need to be referenced.
* Better integrity enforcement with simple foreign key constraints.

If fact, in (guessed) 50% of the queries I could have avoided at least 2
table joins!

Think big. That is why...

- Joris Dobbelsteen