Random Primary Key Generator

[BFD]

One of the methods my company comes by customers is by rescuing failed development projects (often, they are projects on which we lost the initial bid - sweet, profitable justice). While digging through one such project, one of my coworkers discovered a real gem of an algorith - apparently the original programmer had a "brilliant" idea for an algorith to randomly generate a unique fixed length, number string to be used as a primary key. Essentially, it takes a 4 digit random number, appends 2 leading zero's, then queries the SQL Server database to make sure it doesn't already exist (and repeats if it does exist). And if they run out of numbers (the base is only 4 digits), there is no logic for stopping the endless loop. So if the website is successful and 10,000 records are added, the website will permanently go into lala land.

Given the algorith itself is rather absurd, I have been left wondering just what the purpose would be for having a randomly generated primary key for a table? Can anyone think of a possible use?

[Charles Stover]

There isn't one. They should have just used auto_increment, which would have effectively also logged the order the users registered and given a loop-able (x = 1; x <= max) list. Along with the fact that if only one number isn't used (e.g. 1 - 5940 are set and 5942 - 9999 are set, but 5941 isn't assigned yet), then it would take AGES for the algorithm to randomly generate that number.
So, that was a pretty fail method on the programmer's behalf.

[Bob]

Mar 11, 2010 15:46:45 GMT -8 BFD said:

One of the methods my company comes by customers is by rescuing failed development projects (often, they are projects on which we lost the initial bid - sweet, profitable justice). While digging through one such project, one of my coworkers discovered a real gem of an algorith - apparently the original programmer had a "brilliant" idea for an algorith to randomly generate a unique fixed length, number string to be used as a primary key. Essentially, it takes a 4 digit random number, appends 2 leading zero's, then queries the SQL Server database to make sure it doesn't already exist (and repeats if it does exist). And if they run out of numbers (the base is only 4 digits), there is no logic for stopping the endless loop. So if the website is successful and 10,000 records are added, the website will permanently go into lala land.

Given the algorith itself is rather absurd, I have been left wondering just what the purpose would be for having a randomly generated primary key for a table? Can anyone think of a possible use?

The only plausible purpose I can think of is portability. Not using an auto increment means one less restriction to worry about should the application change databases. That said I think the likelihood of moving to a database (with the exception of flat files) that doesn't support incrementing is next to null.

[Charles Stover]

Even then, his algorithm could use increments instead of random.

On a database without auto_incrementing:
$id = mysql_query('SELECT id FROM members ORDER BY id DESC LIMIT 0, 1');
$id = mysql_fetch_assoc($id);
$id = $id['id'];
mysql_query('INSERT INTO members (id, username, password) VALUES (' . ($id + 1) . ', "new_user", "password1")');

So there really is no need for that.

[BFD]

Mar 11, 2010 16:00:33 GMT -8 Charles Stover said:

There isn't one. They should have just used auto_increment

Or NewID(), which generates a uniqueidentifier.

which would have effectively also logged the order the users registered and given a loop-able (x = 1; x <= max) list. Along with the fact that if only one number isn't used (e.g. 1 - 5940 are set and 5942 - 9999 are set, but 5941 isn't assigned yet), then it would take AGES for the algorithm to randomly generate that number.

That was my point exactly. The more data that gets added, the slower it responds.

So, that was a pretty fail method on the programmer's behalf.

Yep. The only good aspect to programmers like this is they make even mediocre programmers look good. Unfortunately, they tend to charge low rates, but bill a ton of hours before they get fired.

[freeware2dl]

Mar 11, 2010 16:12:02 GMT -8 Charles Stover said:

Even then, his algorithm could use increments instead of random.

On a database without auto_incrementing:
$id = mysql_query('SELECT id FROM members ORDER BY id DESC LIMIT 0, 1');
$id = mysql_fetch_assoc($id);
$id = $id['id'];
mysql_query('INSERT INTO members (id, username, password) VALUES (' . ($id + 1) . ', "new_user", "password1")');

So there really is no need for that.

very useful for me thanks

[Charles Stover]

Mar 15, 2010 21:59:21 GMT -8 @freeware2dl said:

Mar 11, 2010 16:12:02 GMT -8 Charles Stover said:

Even then, his algorithm could use increments instead of random.

On a database without auto_incrementing:
$id = mysql_query('SELECT id FROM members ORDER BY id DESC LIMIT 0, 1');
$id = mysql_fetch_assoc($id);
$id = $id['id'];
mysql_query('INSERT INTO members (id, username, password) VALUES (' . ($id + 1) . ', "new_user", "password1")');

So there really is no need for that.

very useful for me thanks

If you're looking for a way to generate user IDs, then just use the AUTO_INCREMENT ability of MySQL. My post was just an overcomplicated alternative that does the same thing.