After participating in a Facebook discussion on the topic of how to generate
The AutoInc value
The AutoInc value is in itself very simple. Just a value that gets increased for each row added to a table. 1, 2, 3 and so on. This is not so bad, of course. Actually, nothing is terribly bad about the AutoInc approach to primary key values. It’s rather the competition to AutoIncs that’s so much more advantageous that sends to old sequences to a much deserved circle of Dante’s inferno.
AutoIncs should never more be used in new databases. And in AutoIncs I include most kind of costly sequence and select-max methods.
Enter the UID
So what should be used instead? The question is simple; use UIDs. A UID (or sometimes called GUID) is a value calculated by difference algorithms on any computer or platform. It’s about 16 bytes in storage size and visibly a 32-38 character string. And it is universally unique.
A common misunderstanding is that UIDs are costly both in terms of the database and in terms of converting it in the application layer. Not so. Although there are different ways and formats of UIDs and different friendly representations (for instance A5504786-F86D-4BAA-ABFF-05A7C3A798D1) only 6-8 bytes are actually stored in the database table or represented in most program languages.
Most databases and programming languages has functions to create a UID and in databases you can often insert them in triggers or such (if triggers are your preference). You can even have them as column defaults in most databases, even if I don’t normally take that approach.
So while some programmers think of identity levels of there primary keys, the UID programmer sits back and relaxes. No need to determine if the ID needs to be unique on a table level, logical partitions of tables or even all tables, as the UID is guaranteed to be unique everywhere, even outside your database. And in fact the entire universe.
Also, if your inclined that way, another advantage UIDs as primary keys are that you can calculate your UID in your app as your user enter a new record. So as the users chooses Create customer (or some other type of data) you already have an ID that you can use internally to connect other stuff to that new customer. When the customer is saved to the database, you can use that exact ID in the database. It won’t clash with any other customer.
And furthermore, when some other department extracts your customers from the database and inserts it in theirs, there is no need to calculate another primary key value and maintain the relationship between these two customer records (that are in fact the same customer) because they have the same ID.
Common criticisms of UIDs
A UID takes up to much space in the database and the table indecies
A UID is overkill
