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
Best Answer
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) Autoin one col andVarChar(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 asindexed, uniquewould 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.