0

I am trying to create a dynamic system when a user can create different objects and relate them to another objects.

I need is to be able to generate an identity column similar to auto_increment but in an alpha numeric format.

The ids will be something like 001abcdeFGhJk123

Using a id like above will help me identify the object identity. For example I can say the first 4 characters are the object id and the last 14 charecaters are the record identifier.

How can I do this in MySQL. Or is it even possible in MySQL. I belive MS SQL server and Oracle database have field type of identifier that does the trick.

Thank you in advance

3
  • 1
    "For example I can say the first 4 characters are the object id and the last 14 characters are the record identifier" - mixing different types of information in a single column is almost always a bad choice. Create three columns one with an autoincrement and make those three the primary key (or unique key). And Oracle does not have an "identifier type that does this trick" Oct 11 '14 at 6:30
  • Salesforce.com are using oracle database and I belive they are using similar strategy in the id.
    – Mike
    Oct 11 '14 at 6:36
  • 2
    Just because some company is using a specific design doesn't mean it's a good design. Oct 11 '14 at 6:43
0

You can have almost any field as the primary key and index it as such. However it would be down to the logic further up the stack (your scripting language most probably to provide unique IDs. You will almost definitely find yourself making additional queries to find unused IDs as the DB grows.

One approach to alpha numeric to numeric conversion would be a key table int(11) Auto in one col and VarChar(18) in the other. Thus you would actually be using common or garden numeric keys on the tables but would have a user handle them in the keys table. You would also have unique IDs across all tables and thus the user key would also implicate table as well. Simply setting the user key as indexed, unique would mean that you would try to write the key and if the row insert was a success then you are good to use the ID (and/or the key depending on what you are doing) but on a fail you would need a new key and could bounce back to the user for a new reference.

However have you considered using numeric keys in the data logic layer but showing user labels in the UI layer thus users can call their object Bob, Harry or Alpha123456FooBar1 without strange plumbing lower down to accommodate them.

In your case you want to show the user a concat key which at the data layer needs to be stored as two separate keys. This concat key seems from what you have said to define a singular one-to-one relationship which is not really it's job. It sounds like what you want is a key to the relationship which brings us back to a lookup table. here you would have the objects table use numeric keys (from the object and other object tables) and provide a unique text value (MD5/SHA1/etc (of the key concat) might work 99.9% of the time) as the label which could be enforced with index, unique.

You could also limit the int size of the object key to four digits (9999 possible entries) in value and (I guess) use VarChar keys in the other table (if you must). Thus if the user requests 0001abceFGhJk123 you can split that into 0001 (1) and abceFGhJk123.

This is far from ideal as numeric auto provided keys are so much easier to deal with. Then again you could create a lookup table of separate keys and concat-key/label (as per the universal ID I mentioned before) if that is really important to you.

One workaround (if you are determined that the users must be able to have alpha numeric keys but you know that numeric keys are easier at the data layer) is to use numeric keys at the data layer and convert the numeric base to show letters to the user. For example shifting to base16 would give you 0-9 and a-f (case insensitive) and this sort of conversion is built in to most languages, or base36 would give you 0-9 and a-z (case insensitive) while base62 would give you 0-9 a-z and A-Z although to be honest you would need a significantly huge data set to make a 14 character data stub that was not mostly padded out with zeros. MySQL lets you override the auto value with a number of your own (provided it does not yet exist) and if these key values are coming from users shifting down from a base to base10 might work.

Whatever approach you use you will be very grateful, down the line, that you kept it simple at the database layer and kept your keys separate for actual primary key data.

1

You have to use UUID() function that guarantee that returned string is unique not only within your table or database but all over the world.

http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_uuid

A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.

Also there is UUID_SHORT() function that returns 64-bit INT instead of string, that is way faster for indexing and search.

http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_uuid-short

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.