Aaron > personally > think that it's a choice better made by dataset, rather than database > wide.
This is not a good idea unless your db design is static as one of the criteria for making this choice is the number (existence) of outgoing relations from a table, therefore if you decide to have a table with a natural key any outgoing relationships from this table will have this structure unless you refactor it Neven > > 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] > > > -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected]
