Unique Id Generation

Question

How can i generate a unique key of length at most 12 character with these attributes: Name, Father's name, Mother's name, Date of birth, Place of birth.

Thanks in advance :)

Answer 1

hmm.. which DBMS?

Anyway, Concatenate the aforementioned strings with the result of the UUID MySQL function, and perform a CRC32 function on the result.

Then, subtract the first 12 characters.

Answer 2

Hi

That's not a key you describe, but a short piece of structured data that you want to use instead of a key. Perhaps more helpfully:

-- do you want to be able to recover those attributes from a given 12-character key ?

or

-- will you be happy to only generate a key given those attributes ?

Regards

Mark

Answer 3

Even those columns probably aren't sufficient to be unique, and since a person's name can change (with marriage, choice, etc), I typically would not use that to generate a unique key.

I typically use a surrogate, like an autonumber/identity/sequence or a UUID/GUID, depending upon requirements.

Answer 4

If you need same attributes to produce the same key, concatenate all attributes to a string, calculate a SHA1 hash and trim first 96 bits.

This is not guaranteed to be unique, but 50% probability of a hash collision will be at 3.3E+14 values which is more than enough.

If you don't need this, just use a surrogate ID.

Answer 5

The main, and in fact only, attribute of a unique identifier is that it is unique (within the scope of consideration, be that the table, the schema, or global). Incorporating any "real world" values into such an id does not make sense.

You should create a unique constraint on the attributes you mention, if you're sure that those attributes uniquely describe a person. This combination of attributes forms the "natural key" of the table.

The unique ID is a "surrogate key", a convenient handle that saves you from having to specify all the columns of your natural key in joins with that table. How you obtain a unique ID is database dependent. In Oracle, for instance you could use a sequence in combination with an insert trigger.

The unique constraint on the natural key will prevent you from inserting the same person twice, but you should really check the table first to see if the person already exists. If so, use the ID that was already assigned from that point on. You can, of course, combine this into a procedure where you give it your fields and it will return the ID, hiding the act of looking it up, or creating it if it's new and then returning the new ID.