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
Cheers,
AD
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
