Re: Create on insert a unique random number

On Tue, 18 Mar 2008 13:57:39 -0700
Steve Midgley <public(at)misuse(dot)org> wrote:
> At 12:36 PM 3/18/2008, D'Arcy J.M. Cain wrote:
> >On Tue, 18 Mar 2008 12:23:35 -0700
> >Steve Midgley <public(at)misuse(dot)org> wrote:
> > > 1) Create a second field (as someone recommend on this list) that
> > is an
> > > MD5 of your primary key. Use that as your "accessor" index from the
> > web
> >
> >I strongly disagree for three reasons. First, if you are going to
> >generate a key then don't store it. Just generate it every time.
> >Second, don't generate it based on a known field. You may think that
> >it is secure but what if you private key is compromised? Do you then
> >change everyone's security code? Third, what if one person's
> >code is compromised? If it is based on a calculation then you
> >can't change that one person's security code.

> I'm not clear on your concern here - an MD5 hash doesn't have a private
> key that can be compromised, afaik. It's a one way hash. I don't see

Right so it is even less useful than I implied. It can never be
changed so why store it when it can be re-generated at any time.

> much difference between making an MD5 of the primary key and generating
> a random number for the "public primary key", except that you shouldn't
> get index collisions with the MD5 method (whereas eventually you will
> with a random number, though of course using a GUID would eliminate
> that concern for practical purposes).

But your suggestion was to base this key on the serial primary key so
where is your index collision protection? You are going to get
collisions on both the serial key and, to a lesser extent, your
generated one. Besides, has anyone ever demonstrated a real issue with
lookups using serial primary keys? I think you are trying to second
guess the database engine with this and I don't think that that is a
great idea.

> The issue is about creating an index into a sparse hash so that each
> record is somewhat randomly located in a sparse hash "index space".
> (One valid reason to do this would be if you wanted to hide the total
> number of records in your table from competitors or customers). (Just

If that is your goal then start your serial at something other than 1.
Start at 1,000,000 for example and your first user will think that
you already have one million clients. Actually, he will think that
you started elsewhere than 1 but he won't know where.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.