On Sat, Sep 12, 2009 at 12:35 PM, 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 it's a fine idea so long as you understand:
*) the index(es) will be larger
*) referring tables will have to be updated (via RI) if/when the email
address changes
*) lots of people will tell you that you are doing it wrong :-)
*) if you need to specialize p-key further (add another field), it can
be a big change
on the plus side:
*) you get to drop an index because you obviously had to index the key
serparately
*) if you query a referring table and are interested in email address
(and no dependent props), you get to skip a join
*) sorting be email address can be free
*) less data transfer headaches
merlin