Wednesday, November 21, 2007

Totally unique ID

How do you set your database ID? Well, as we all already knew ID is something that differentiate one record to other records in a table. Without ID, it will be difficult to determine which row / data we take from database.

There are some technique used to create IDs. Usually we tell the user to enter unique form of text on the ID field or use Auto Number?

Well, I have a very different opinion about this, why? After creating a table, I usually add a column named ID and use it as primary key. This ID is for the system, not for the user. Which means, the user never sees this column. User sees different ID, such as EmployeeID, studentID, teacherID, etc and keep it unique and can be editable, but the system use ID in the transaction.


Now, how to determine the ID is definitely unique? Moreover if we are developing a system that can be put in two different places (eg. main office and branch office) but need to have the ability to merge the data. Using user defined ID and autonumber is not recommended since duplication happens many times in sychronization stage.

So, what is the solution? We can use Date/Time stamp as ID, but only when your system can support milliseconds, otherwise, you have to make sure the user entering new data only once in a second, otherwise, duplication will happens. This is the best practice to create an ID in a table.

Using Date/Time is actually good enough, because it support almost infinite number of data, well, in what year days will stop right?

Working with MS Access is very frustrating. Only able to do simple query and Date/Time not support milliseconds. So, what is the solution? I still use date/time, only now, I have to format it to YYYYMMDDHHmmss then add few digit random number after it. To make sure there are no duplication, I use the same length digit number as the date/time format (14 digits) for random number. To make it more unique, I convert it to Hexa.

There, thinking about the possibility, it's gonna be difficult to find duplication since every seconds has 10^14 probability.

No comments: