Aaron
I'm a database designer and would suggest that unless your database is
very simple you should avoid composite (natural) keys at all cost. This
does not mean that you can;t define a composite candidate key on a
table. The main advantage of a 'natural' key is to avoid the overhead of
linking to the source table to get a meaningful value for the key, This
logic unfortunately falls over as not all objects in a model have an
obvious natural key and secondly you usually have to link to the source
table for other data
The main reasons (and i found this out on a db I designed and maintained
for years) against composite keys that as the database gets more
complicated your joins become verbose ie
Select a.col2, b.col2, c.col3, d.col3
from
table1 a join
table2 b on b.table1id = a.table1id join
table3 c on c.table1id = b.table1id and c.table2id = c.table2id join
table4 d on d.table1id = c.table1id and d.table2id = d.table2id and
d.table3id = c.table3id
Do this for 6 tables and you get the idea (also drop a single join
clause and it can be painful)
With surrogates your database is infinitely extensible, toward the
'fringes' if you are coding an enumeration table you may be tempted to
simply put in a natural key, I'd not for the following reasons
1. It gives little benefit, an enumeration table is usually small and
therefore the overhead of a surrogate key join is usually done in mem and
2. It 'closes' that path of the design
As far as 'link' tables go, (ones that resolve a many to many) again put
in a surrogate key as well as a composite unique candidate key of the
foriegn keys, for the same reasons above
HTH
Neven
I've seen them alot, worked with them rarely. I was just hoping to
open a discussion with people working with experienced DBA's regarding
today's standing on the use of composite primary keys.
I ask, as I have been given a schema to work with, and will be using a
framework for development. CakePHP doesn't support composites at all,
and I read alot of troubles in other frameworks to work around them.
(to the point of hand rolling queries)
Call me lazy, but it just seems to me that in most cases, a singular
PK can be found for pretty much any table. But are they are must in
certain situations?
Regards
Aaron Cooper
--
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
--
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
