A natural key is a key that comes directly from
data in source system (and it is not generated). As it already exists in the
real world (case of the social security number, for example), and it is made up
of real data, with business value, we may need to rework it when the business
requirements change. It is a common value, having a relationship with the rest
of the column values to a given data record.
Additionally, and although it uniquely
identifies a single record in a table, when we are dealing with data from
multiple source systems, we could have duplicates, depending on what is our
natural key: imagine having two different customers, one from source system A
and another from source system B, with the same CustomerId.
Another important aspect of natural keys is
that they are usually strings, not integers, and because of that they are
larger in size.
In contrast, a surrogate key is a database
system generated key (typically at run time before the record is inserted into
a table) and, consequently, it is an artificial and unnatural value added to
the data schema that uniquely identifies a single record in a table.
Without
business value, it is not meaningful to users, but it is more efficient for the
database system to use, improving query performance, simplifying joins as it is
commonly an integer assigned sequentially (easier to store and index in a
database). Independent from business changes, it is easier to maintain without
the need to be updated.

Comentários
Enviar um comentário