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]
