Re: counting query

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/28/07 08:36, John Meyer wrote:
> 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?

This is the great synthetic-vs-natural key debate.

Sure, it's easier to write queries that join on a synthetic integer
field.  However, adding 3 extra fields to a few other tables is not
onerous, and it adds useful information to the other tables, since
(entered,timeperiod,studentid) are what *really* makes a record
unique.  Also, synthetic keys mean that you have to do more joins,
since if you want to know about "entered" and table T_FOO, you'd
have to join "attendance" to T_FOO.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFvMH/S9HxQb37XmcRAoefAKDpf/6TG5WzP4nBIEcqVHE1dmb4/gCgxkZd
5fxfG4NoBR/Ul3fhqmpuTFQ=
=g/F2
-----END PGP SIGNATURE-----