A relevant puzzle is the classic birthday problem. How many people do you need in a room (on average) before you're likely to have two with the same birthday? Most people will guess 366 or 180. Want to think about it? The answer may surprise you.

This is the same problem you face when designing a scheme to create random unique identifiers. How many digits does your identifier need to give you breathing space to create many records and have no two be the same? Here is the math to figure it out, and let's use the birthday example because the numbers are easier to wrap our heads around. In the following equation, 365 is the number of possible birthdays, and n is the number of people in the group. The probability that two will have the same birthday (a conflict of unique identifiers) is:



Now, I don't want anyone's brain to explode, but it's important to understand how large a number 365! really is, before you start to think FileMaker or your calculator can do that math. (I have two different HP scientific calculators, and neither of them could do it.) Written out normally, 365! looks like this:

2510412867555873229292944374881202770516552026987607976687259519390
1106138220937419666018009000254169376172314360982328660708071123369
9798534453679106538723835997043555327409376780914914294408643160469
2507451013484702554601409800590796554104119549610531188617337343514
5517193282760847755882291690213539123479186274701519396808504940722
6070330012463283988005504874279998766904169734378610781853446679668
7151104965388813013683619901052918005612584454948864861768291582634
7564148990984138067809999604687488146734837340699359838791124995957
5845388736166615330932535512568450560463887381297029513811518614136
8892298651000544094394301469924411255575527914076049276425374025041
0391056421979003289600000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000

In scientific notation, it's about 2.5 × 10⁷⁷⁹. In other words, about a trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion trillion times as many protons, neutrons, and electrons than there are in the entire universe (generally accepted to be about 10⁷⁸). Keep this in mind as I go on to discuss numbers even larger than the above by more than a googol of magnitudes.

The practical application of all this math is to find out whether, say, an eight-digit random number (0 through 99,999,999) is good enough to use for a unique identifier. Let's say you expect a maximum of 10,000 records to ever be created. The probability there would be a duplicate among 10,000 random numbers between 0 and 99,999,999 is:



Yes, eggheads, you can also write this as a permutation, but let's stick with one thing at a time. Now, to get the answer, I had to rely 10 percent on Wolfram Research Mathematica and 90 percent on my friend Diana Richards Doyle, a professor of game theory at the University of Minnesota. After you simplify this and run it through Mathematica (or your math program of choice), you get a probability of .39345 that there will be a conflict (an event with a probability of 0 can never happen, and a probability of 1 is a sure thing). This means if you expect 10,000 records, an eight digit random number isn't large enough to be reliably unique.

Just how big do you have to go?

I consider one tenth of one percent, .001, to be an acceptable risk of duplicate identifiers. To find how large you have to make the pool of possible random identifiers so the 10,000 records have a .999 chance of all being unique, you'd solve for x where x is that maximum number:



Suffice it to say that when you have to use random numbers as unique identifiers, you max out FileMaker's 20-character limit and use the following, which gives about 13 novillion () possible combinations, and it's never failed me yet:

Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1) &
Middle ("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789", 1 + (Random * 36), 1)

Oh, and that birthday problem from before? The answer is about 23 people. In any group of that size, it's even money that two will share a birthday.