Talking key

from Wikipedia, the free encyclopedia

A speaking key ( speaking number , natural key , intelligent key ) is a key candidate with a logical relationship to one or more attributes of a table structure which, in contrast to a composite key, are stored in a key field.

The natural key is in contrast to the artificial key , which is generated by assigning unambiguous numbers or character strings and which has no such logical relationship to the content of a data record.

functionality

The speaking key, as a carrier of meaning, refers to the corresponding entity values (facts per se) and characterizes them using rules encoded in digits and characters, which refer to certain properties (attributes) of the respective entity value. For example, information on the gender or date of birth of a customer can already be stored in the customer number (the " key " for finding additional customer data).

One of the main advantages of a talking key is that it is easy to memorize, that it often already exists and that there is no need to add a new, unnatural column to the schema.

Advantages and disadvantages

One problem with speaking keys is that, sooner or later, they tend to break . It is said that the logic for key generation breaks when a new entity value is to be inserted into a table whose primary key is identical to that of an already existing entity value. Speaking keys are therefore only partially suitable as primary keys in the data modeling of relational databases . Often, however, they are used as a search key, which then does not necessarily have to uniquely identify a certain data record.

Another disadvantage of a meaningful key is that the key can change as business requirements change. This can result in a time-consuming re-encryption, which leads to a time-consuming data migration .
If z. If, for example, a numeric customer number is used as the primary key and this subsequently has to be converted into alphanumeric due to business-related requirements (e.g. integration into a new parent company), not only the data type of the table column has to be changed, but it has to be changed to The column attributes are changed in all tables, which refer to the customer table as foreign keys. While such a change is carried out quickly in the design phase, the conversion work is much more time-consuming if data is already loaded in the database, especially if the speaking key was used as the primary key and is referenced by other tables.

Examples

Swiss AHV number (before July 2008)

In Switzerland, the prime example of a speaking key is the old AHV number of the old-age and survivors' insurance , which was problematic because firstly, the uniqueness over time is not guaranteed, but only for people currently living and secondly, the number changes when a name changes can. Since July 2008, the new AHV number has been 13-digit, random and anonymized. The new AHV number has become a 'non-speaking' number.

German insurance number

The German insurance number is also an example of a speaking key with all the problems that arise from it. From certain digits one can derive the gender, the year of birth and the first letter of the family name. When this numbering system was defined in 1964, it was believed that these characteristics were constant.

  • today it happens that people change their gender ( transsexual law ).
  • There are even cases in which a registration office had to change the date of birth. Some German resettlers from Russia intentionally gave the wrong date of birth in order to be allowed to enter Germany. You were given an insurance number containing the incorrectly given date of birth. Some of these people later had their actual date of birth entered on their ID.
  • At that time the men were mostly employed who kept their surnames when they got married. Today, firstly, many women are employed who often change their surname when they get married, and secondly, today men can also take the surname of the woman when they get married.

The cases where a person's underlying facts changed became more common over time. In the beginning, the insurance number was also adjusted in any case, but due to the administrative effort involved, such adjustments are only made at the request of the insured. Today the insurance number is still issued in the same way, but the number can no longer be called really 'speaking'. If you had decided at the time to simply assign a sequential number, then the requirement would never have arisen to have to change this number. In addition, you would get by with less than 12 digits.

Further examples