If you do a search for Unique Record IDs you will probably find several hits, as it is one of the common problems in data application design, many use a serial number or hashed random number with some check to verify that there is not other occurrence of the number in the table… But what if you have a system that will merge data from intermittently connected tables from different locations (aka a distributed/replicated database) What you want is each instance of the database to generate an ID unique enough from its peers so there is no conflicts when merging. WANDA's predecessor faced that challenge as it was written before we had LAN and then inter-office VPN connections.

I found also while merging diverse datasets I needed a method to track down problems in the system or data, so I merged in a fingerprint into the IDs. Which gives me an idea of who initiated and when data was created, this means I rolled into my ID a compact timestamp as well as a method of identification.

A couple limitations I had on creating my ID are, it can only contain letters (upper and lower case) and numbers, and be at maximum 15 characters in length.

I wanted to have a couple elements for unique identification as well as be able to handle several inserts in a second (a much earlier version had issues once the database was ran on faster hardware as well as the DB supplies unique ID function)

This is what I have now:

YYMDHMSCCZZZUUU

(Y=year, M=month, D=day, H=hour, M=Minute, S=Second, C=counter, Z=Zip, U=UserID)

The elements are encoded base 62 where the order is 0-9,A-Z,a-z

Year goes up to 3844, 0-12 for month is no problem, as well as day, hour, minutes, and seconds, the two digit count is to prevent collisions from rapid INSERTs (I really doubt my system would do anything that could max out the 3844 inserts in a second needed to trigger wrap around), the next two blocks are location and user IDs, zzz is an encoded 5 digit zip of an office, and the UID is a numeric user ID which allows for up to 238,328 users.

Another side benefit of this ID is a sortable time stamp w/ID, which I have employed as an authorization id for one table. For this purpose I have a decode function available to read the IDs.

As I mentioned in the previous post this could be used for a historical record ID, which a much finer resolution then the date I am employing now.

In MYSQL VARCHAR and TEXT fields treat mixed case text as if it were all upercase (better for sorting names), to get distinct upper/lower case in searches you need to set your ID fields as VARBINARY fields which upon select will distinguish the upper and lower case letters.

Well thats it for IDs, I hope that gives you inspiration for creating unique IDs in your system.

~~LINKBACK~~ ~~DISCUSSION~~