I know Unique Keys are as basic as the Table itself, but I wanted to review my choice of setting a unique key in light of the new UUID field type. I wanted to see what you think about the four methods I describe, and get your suggestions on the best approach.
AUTOINCREMENT
The most common method to create a unique id is the autoincrement field rule. But I refuse to use this method for one good reason -- someone could delete the 'current highest value' making the next entry a duplicate of a previous (deleted) record. That is, 1001 may be assigned to Steve Wood, and then later on 1001 could be re-assigned to John Smith because Steve's record was deleted.
But somewhere, maybe a sales transaction table, the value 1001 may have been assigned to a sales record. It USED to be attributed to Steve Wood, but now erroneously attributed to John Smith. The sales record is really an orphaned record, but you don't know it because a new parent record has been created. Yes, this could be avoided by perfect administration of records. But that is not always possible and accidents happen.
UUID
This is what I converted to recently. It works great. There is no chance that a new record will have a UUID that ever existed before. Alpha recommends it as a good candidate for a unique key. An 'orphaned' record, like in the above example is truly orphaned and you can deal with it appropriately. The only problem I have is the field is really, really BIG (32 characters). It's overkill since I only need a value that is unique for the table, not one unique in the galaxy. I wish there was a UUID_LITE() that was only unique for the table.
In the case of something like an Invoice ID, its way too long. Could not expect someone to recite that value when calling in about their invoice. So, in that case you end up needing the UUID and still a shorter autoincrement for practical use.
HEX(TIME)+RAND())
Some variation of time + random number, perhaps converted to hex. For example:
?dec_to_hex(val(time("MMddyyyy0h0m0s3")))+floor(rand()*10000)
= "239AF6A026D9887070"
Shorter, still unique, partly seqential. A little easier to guess since the first 11 or so will be the same when generated during the same minute.
Lastly, SSN, TelephoneNumber, subset of Client Name, etc.
I won't even consider these examples for unique ids for internal use in linking parent and child.
What do you think?
AUTOINCREMENT
The most common method to create a unique id is the autoincrement field rule. But I refuse to use this method for one good reason -- someone could delete the 'current highest value' making the next entry a duplicate of a previous (deleted) record. That is, 1001 may be assigned to Steve Wood, and then later on 1001 could be re-assigned to John Smith because Steve's record was deleted.
But somewhere, maybe a sales transaction table, the value 1001 may have been assigned to a sales record. It USED to be attributed to Steve Wood, but now erroneously attributed to John Smith. The sales record is really an orphaned record, but you don't know it because a new parent record has been created. Yes, this could be avoided by perfect administration of records. But that is not always possible and accidents happen.
UUID
This is what I converted to recently. It works great. There is no chance that a new record will have a UUID that ever existed before. Alpha recommends it as a good candidate for a unique key. An 'orphaned' record, like in the above example is truly orphaned and you can deal with it appropriately. The only problem I have is the field is really, really BIG (32 characters). It's overkill since I only need a value that is unique for the table, not one unique in the galaxy. I wish there was a UUID_LITE() that was only unique for the table.
In the case of something like an Invoice ID, its way too long. Could not expect someone to recite that value when calling in about their invoice. So, in that case you end up needing the UUID and still a shorter autoincrement for practical use.
HEX(TIME)+RAND())
Some variation of time + random number, perhaps converted to hex. For example:
?dec_to_hex(val(time("MMddyyyy0h0m0s3")))+floor(rand()*10000)
= "239AF6A026D9887070"
Shorter, still unique, partly seqential. A little easier to guess since the first 11 or so will be the same when generated during the same minute.
Lastly, SSN, TelephoneNumber, subset of Client Name, etc.
I won't even consider these examples for unique ids for internal use in linking parent and child.
What do you think?
Comment