Thanks all. This discussion has forced me to look deeper into the argument
online also.
I see the pros and cons of composite/natural/surrogate keys, and personally
think that it's a choice better made by dataset, rather than database wide.
The schema I have recieved has not been done by a DBA, nor does the provider
pretend to be (although he is an experienced project manager).
An example of what has caused my concern here is a table called "Devices"
which will store information on individual physical electronic devices. He
has made the primary keys DeviceID, CustomerID, CountryID and DeviceTypeID.
This seems to be a massive overkill even in my limited DB design experience.
Each device will appear only once in this table, so why is the DeviceID not
good enough alone?
Regards
Aaron Cooper
----- Original Message -----
From: "Keith Allpress" <[email protected]>
To: <[email protected]>
Sent: Monday, March 15, 2010 11:30 PM
Subject: RE: [phpug] [OT] Composite Primary Keys
This is really a question about the value of surrogate keys over natural
keys.
For a primary key the surrogate won't guarantee uniqueness so the cost is
its index, as you still need an index over the natural columns.
Surrogates can have advantages for foreign key relationship as when types
are altered the relationship structure can be unaffected. The role of keys
is reduced to cardinality - Structural transparency results.
Another good use is to achieve simplicity, consider a self-join into a
composite keyed table.
Keith.
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf
Of lenz
Sent: Monday, 15 March 2010 8:54 p.m.
To: [email protected]
Subject: Re: [phpug] [OT] Composite Primary Keys
if gmail would have a "like" button i would so like this post ... sums
it up really nicely :-)
cheers
lenz
On Mon, Mar 15, 2010 at 4:30 PM, Richard Clark <[email protected]>
wrote:
The answer to your question depends on what you actually want out of
your database. If you're looking for a dumb storage for an object set,
as most ORMs act, then having a single primary key for each table is
vital because each row is a distinct object. There's no point fighting
this, just add a serial/auto_inc column or whatever your ORM likes and
go with the flow.
If on the other hand, you'd like a relational database that can use
its knowledge of the dataset and schema to optimise the insertion and
retrieval of information in an intelligent fashion, then composite PKs
are a very effective tool. They're not a tool you use everywhere, but
where you want them, they do wonders and I'd certainly never give them
up myself.
I don't use ORMs at all. They're inefficient at best, and hideously
inefficient at worst - even relatively smart ones like SQLAlchemy. The
specific rule "Those who don't understand X are doomed to reinvent it
poorly" has never been more relevant than when applied to SQL.
Regards,
Richard.
On 15 March 2010 16:11, Aaron Cooper <[email protected]> wrote:
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]
--
twitter: @norbu09
current project: iWantMyName.com
painless domain registration (finally)
--
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.790 / Virus Database: 271.1.1/2746 - Release Date: 03/14/10
20:33:00
--
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]
