A surrogate key is a substitute primary key for when:
Since the value has no meaning, there is unlikely to be any demand for a change making them true primary key.
Surrogate means to choose in place of another
It's a primary key that holds a generated value.
A surrogate key exists almost always next to a business key.
SELECT * FROM tbl_name
WHERE surrogate_pk=MD5(urlValue)
AND url=urlValue;
A surrogate key needs to be generated, if there are:
Numeric values also produce shorter values of predictable length. Storing numeric codes in place of string is more space-efficient.
The use of surrogate keys (SKs) in dimensional modeling is not only the simplicity of star design or performance.
You often need to populate dimensions with extra records not found in the source table, such as UNKNOWN or NOT APPLIES records
These records do not have any natural identifier.
If the id are sortable by time, a list entity could be sorted without fetching extra information.
Example:
IDs should ideally be short enough.
Database system are generally limited at 64 bits to store integer
collision probability should be acceptable
Surrogate key may be implemented via:
A sequence (auto-incrementing number) in a database is suitable in a centralized architecture. If the id can be generated decentralized, you need to add more information to make them unique (ie the time and node name for instance as instagram does)
Server-side generated identifiers are pretty much guaranteed to be unique.
Via the standard guid outside a database (distributed)
Bootstrap implements it as a completly random number 1)
const MAX_UID = 1000000
const getUID = prefix => {
do {
prefix += Math.floor(Math.random() * MAX_UID)
} while (document.getElementById(prefix))
return prefix
}
Generated outside a database:
In datawarehouse, they may implements a not/so real surrogate key as a hash of the value of all columns.
device fingerprinting is also a generated way to identify an individual.
Nanoid is a random ID string comparable to UUID v4 (random-based)
If the number of id generated is small, you can reduce the size: Collision probability by size (See also Collisions of Hash or Identifier Generation)
const { nanoid } = require('nanoid');
nanoid(15); //=> "YrxC0J7fBQobaL-"
Universally Unique Lexicographically Sortable Identifier
Canonical String Representation
ttttttttttrrrrrrrrrrrrrrrr
where
https://segment.com/blog/a-brief-history-of-the-uuid/