This is one of important theory for all Database Architecture and Designer.
Generally, we have one common practice to create a sequential, unique number for a database system.
The best practice is to create an integer primary key for each and every table which represents the tuples.
But for this statement, one Question Mark (?)…
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 one stand as a unique string.
A UUID is a 128 bit number and it used to uniquely identify different type of entities.
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 like: network address, timestamp of the transaction and other component details.
The network address and timestamp of transaction is 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 require 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 and it should not create any collisions with destination system.
A UUID solves this problem.
If the system is not online, offline user also can generate a UUID at the client side for few operations.
A UUID always unique for all the systems which resides 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.
A 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 conditions we can represent and 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 suggest you to create an integer sequential primary key and create a separate column for UUID.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.