This article is half-done without your Comment! *** Please share your thoughts via Comment ***
I am Sharing an important theory for all Database Architects and Designers.
We are following a common practice for creating a sequential, unique number for a database system.
The best practice is to create a primary integer key for every table which represents the tuples.
But you should also know about UUID.
You can visit this article on The truth about Primary Key.
What is UUID and How it generates Unique Number?
UUID – Universally Unique Identifier has also stand as a unique string.
A UUID is a 128-bit number, and it used to identify different type of entities uniquely.
The computation of the UUID is quick and easy to generate an ID which can be reasonably assumed to be universally unique.
The UUID is globally unique because it contains a unique combination of the network address, the timestamp of the transaction and other component details.
The network address and timestamp of the transaction are enough to ensure uniqueness.
Example, The standard form of UUID. It is total of 32 digits and representing the 128 bits.
Why we require UUID?
When we are working in the distributed environment system, we need UUID which identifies the integrated data from the different systems.
We have two systems, System A and System B. Now I want to import some data from System A to System B without changing an ID of the source system. System B has already set of stored IDs which might conflict with System A IDs.
The UUID solves the above problem.
If the system is not online, the offline user also can generate a UUID at the client side for few operations.
A UUID always unique for all the systems which reside in a distributed environment.
System A inserts a record with ID 10 and at the same time System B also inserts a record with ID 10.
Now, this is a collision.
The UUID solves this problem.
If the system is accessed by multiple threads, each thread has to wait for new IDs.
A UUID also solve this problem because it does not depend on the previously generated key.
We can generate trillions of UUID in a year and it has very less chance of duplication.
If we recreate a Database or Table, again sequential ID starts from 1. In this kind of situations, we can use UUID.
The index fragmentation is one of the major drawbacks of using UUID random keys.
We should not use UUID as a Primary key of a table. Still, I would suggest you create an integer sequential primary key and create a separate column for UUID.