On 2/27/07, AD7six <[EMAIL PROTECTED]> wrote: > > On Feb 27, 11:36 am, "Adrian Maier" <[EMAIL PROTECTED]> wrote: > > On 2/27/07, AD7six <[EMAIL PROTECTED]> wrote: > > > > > On Feb 27, 8:42 am, "Adrian Maier" <[EMAIL PROTECTED]> wrote: > > > > On 2/27/07, Langdon Stevenson <[EMAIL PROTECTED]> wrote: > > > > > > > My understanding is that Cake 1.1.x.x doesn't support multi-column > > > > > keys. > > > > > > > This feature request in track:https://trac.cakephp.org/ticket/1293 > > > > > indicates that the priority for this feature is very low and unlikely > > > > > to > > > > > be implemented. > > > > > > > Are you working with a legacy database? Or is this your preferred way > > > > > of building the primary key? > > > > > > Is this still true for Cake 1.2.x.x ? > > > > > > You might find this article interesting. It is about surrogate primary > > > > key ( 'id' - integer ) > > > > which are sometimes convenient (from a performance point of view) but > > > > are in fact > > > > an evil compromise because such a primary key *does* allow row > > > > duplicates : > > > > > Does it? I always thought not including a unique index on a table's > > > data allowed duplicates. > > > > It looks like we agree ... duplicates are possible if you rely solely on > > the > > surrogate id and you don't add an unique index to enforce the real primary > > key. > > > > In fact , a table frequently will have are two primary keys: > > - the surrogate one (numeric, or fast update/delete) > > - the real one , which uniquely identifies a row based on the entity's > > properties ( a person's social number, a book's ISBN, etc ). > > Since the primary key can be only one, people will normally define > > the id as "primary key" and an "unique index" for the real key . > > > > But primary key and "unique index" is basically the same thing : the > > difference > > is that a table can have only one primary key , while the unique indexes > > can be more than one. "primary key" is just syntactic sugar for "unique and > > not null" . > > > > > And I feel that's all the article proves. The PK is for identifying a > > > row of data, a unique index is for preventing duplicates. There are > > > times when the two can be one and the same, but in most cases they > > > should not - if only because choosing something that seems to be > > > unique which turns out not to be, or in some cases is null, is so > > > difficult to rectify (quickly). > > > What does this mean (extract from comments by the article author > > > regarding one of the examples of why surrogate keys are bad): > > > "You're correct, though, that because of the size of the key (three > > > columns, one of them up to 100 chars of text) the table carried an > > > INT4 surrogate key which is what we used for joins and application > > > logic" > > > > At work we are writing some modules for a mass billing application (for > > a telephony company) that uses a large database (oracle). For example > > the account_number is a varchar(20) and is heavily used as primary key > > and foreign key. The performance is a serious consideration and yet they > > have chosen to have varchar PKs when their saw fit. > > > > So, i don't think that using other types of PKs is a noticeable penalty > > for small and medium databases . > > Well, the type isn't really a factor, although a numerical field will > sort faster than an alphanumerical field - the difference is rather > small if you are comparing indexing a field of x characters versus a > field of ~x digits. > > In the above example the account number makes a perfect logical > primary key - the row can't exist without one and it absolutely must > not be null. That 'cost' would pay dividends as soon as you did not > need to query the database, or JOIN the account table in, to find the > (otherwise not known) account number from an object that linked to > it. > > However, If you have no logical primary key, a numerical auto-index is > the best idea in most cases unless there is a reason not to. E.g. the > large system I used ot work with used a base64 sequence for almost all > tables to keep key lengths below 7 characters iirc (yes, some truely > massive tables, and unsurprisingly oracle driven). Even if there /is/ > a candidate for a logical primary key it's worth thinking if you can > use it in absolutely all circumstances; there only needs to be one > circumstance whereby the info isn't available at the time of creation > or needs to be null and it shouldhn't be used. E.g. (overused example) > SSN -> what happens with johnny Forigner? > > But to return to the article, the pseudo-message I feel it infers is: > "Use a primary key that has meaning and helps whilst debugging" > > Am I alone in this sentiment? I feel the message should be > "Primary keys are for the database, not for applying some > business rules which may change - make it fast and easy on the DB"
> I also feel that the use of index and key indescriminantly is > confusing and misleading, but in any event I guess we at least kind of > agree :D Yes, i also think that we have no real dispute here. We are are just seeing the issue a little differently. But we both agree that having some unique index around is necessary . And it's quite unlikely for me to need to use a multiple-field primary key in CakePhp. So, even if i think that supporting this feature would be useful , it is not a must-have for me . Cheers, Adrian Maier --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
