Re: primary keys

On Sun, 13 Sep 2009 02:35:02 +1000
Grant Maxwell <grant.maxwell@maxan.com.au> wrote:

> Hi Folks
>
> I'm looking for a bit of advice regarding alpha primary keys.
>
> I have a table (designed by someone else) that has a numeric primary
> key and also a unique non-null email address field.
>
> The use of the primary key is causing me some headaches in that in
> multiple database server environment each server allocates a unique
> number from a range
> (and that works fine) but when the table is replicated (master-master-
> master) the exception handling is a bit tricky because each database
> server may have
> records that are duplicate at the email address field - with a
> different primary key number.
>
> I don't know why it was done this way but it seems to me that the
> email addresses are unique, non null and could be used as the primary
> key. This would make the
> replication much faster and simpler.
>
> Does anyone out there think the change (number to email address as
> primary key) would be a bad idea ?
>
> your thoughts would be appreciated.

I think you have two issues here, and if you identify them as such, you'll
come up with a better decision.

The first is, "Should the email address be my primary key".  And the answer
to that really depends on whether you expect it to change a lot, whether you
have a lot of FK relations and how easy/difficult it will be to manage those.

The second, which may influence the first, is "how do I manage conflicts
when data from different sources is merged?" which is a huge, complex
question.  One potential answer is to replace your integer surrogate key
with a GUID key, which will be unique from all the different sources.  You
could also use a "prefix" system to ensure uniqueness.  Another option is
to use the email address itself.  I'm surprised by your approach, as
personally, I've seen a LOT of people who share a single email address
(husband/wife, for example).  It's not horribly common, since getting
addresses is easy, but it happens, and it's something to consider when
setting this up: if two people share an email address and try to add their
records at different places, how will the system handle it?

In any event, if all those factors tell you that you should switch to using
the email address as the PK, I doubt you'll regret your decision.  Just be
sure to take into account the foreign key factor early on and you shouldn't
have too many problems (in my experience, a lot of people are unaware of
ON DELETE CASCADE and ON UPDATE CASCADE).

Hope this helps.

-Bill