When I first programming back in 1998, I do not have to think twice on what to use as the ID/Primary Key. I used the auto increment number.

Method 1. Use the autoincrement column

Auto increment is an automatic mysql column where the number will be incremented by 1 everytime you insert a record. Hence my ID always start with 1,2,3 and so on. (there is a way to make the number start from say 1000 instead of 1).

Depending on the complexity of your web application, this can be acceptable for most simple apps with less than say 5 tables.

In 2004, I was involved in a quite large project involving more than 50 tables. The spec for the project also needs the ID of many tables (task, leave, meeting, diary etc.) to be inserted in a `calendar` table so the user can see the all relevant records in weekly view (also daily or monthly views).

This is when the problem start to surface. Since all my tables use all the autoincrement number beginning with 1,2,3.., this cannot be done because the autonum is not unique for those records.

Hence Task ID 1 will mix with Leave ID 1 which will also mix with Meeting ID 1. Then I have to move on to the next method.
 

Method 2. Put a prefix to the autonumber and make the ID varchar

This is not bad since the software works fine. So what I did was to put prefix "t" for the task table, "m" for the meeting table etc hence ID "1" will become ID "t1" for task and ID "m1" for meeting

The implication for this method is:-

1. The ID muct be changed from Integer to Varchar.
2. At the time of insertion, you cannot know for sure what ID you will get. Once you have inserted the record, you need to query the record to fetch the autoincrement number, append the prefix and update the record with this new ID.

One problem though, I need to keep track of the prefixes I already used. Once I have ran out of popular alphabets, I started to use 2 characters e.g. ma1, mb2 etc.

Method 3. Use a long 20 character ID including today`s date and current time.

TextBanner

First thing to note, it doesn`t have to be 20 characters. It could be 25, 30 or any length acceptable to your apps.

I chosed 20 characters because I think the risk of creating Identical ID is acceptable to me (see below)

The first 14 characters would be YYYYMMDDHHMMSS (Year Mth Day Hour Min Sec). The remaining 6 characters is a combination of hash (md5) of some variables unique to the specific users e.g. login timestamp.

The typical ID will look like this => 20080522040305a23d38

the first 14 characters (20080522040305) is the current timestamp while the next 6 characters (a23d38) is the first 6 characters of md5 hash (the design decided by you).

You can use your own formula to get the hash for example get the first 6 chars of md5("$loginTime-$Username");

Is this ID uniq?

To answer that question we need to look at the ID. The first 14 characters is the timestamp including the current second. The last 6 characters is a "random" number generated from uniq hash. Hence what is the possibility of accidentally creating the same ID ON THE SAME SECOND?

16 x 16 x 16 x 16 x 16 x 16 = 16,777,216 (almost 17 million)

So the possibility of accidently creating the same ID on the same second for 2 or more different users is 1 in 17 million.

Once you have passed the current time, the second is already incremented, hence there is no possibility for the new record ID to collide with ID created in the previous second.

So the probability of the new ID to collide with previous ID created in previous time (second) is ZERO!

Also the probability for the same person to create the same ID is also Zero because there is no way you can be fast enough to make 2 record insertions in 1 second!

For me this possibility is acceptable. Hence I chosed this method for creating IDs in OfficePhp.

Obviously, if you are Facebook or Google with say, over 20 million users every second, all inserting a new record, you need to devise another method for your ID.

What happen when 2 persons are so unlucky and hit the 1 in 17 million possibility and accidently created identical ID on the same second?

Well, the first person will manage to get the record inserted. The second person unfortunately failed to insert the record.

She might not know about it because in real world, failed insertion due to other factors like slow web trafic, webserver error etc are much more likely to happen (everyday).

What will happen is she will curse the slow traffic and fill again the form and hopefully this time not trapped in the 1-in-17-million-per-second odds.