In dictionary tables, you often encounter the following column pattern:
- An artificial primary key (e.g.,
- An artificial alternative key (e.g.,
- Real data (e.g.,
This pattern can also be found in many other systems (such as Jira or Recurly).
In our example, both tables have a numeric column
id, which is a primary key, a
code of type
char, and real data, such as name or
In our example, both tables have identical fields. However, depending on the situation, the temporary data table can differ from the one in which data is ultimately stored. Sometimes, a simpler structure is sufficient, but in other cases, we may need a more complicated structure.
Why We Need Two Identifiers
At the database level, it is convenient to link objects using numeric identifiers; it is both efficient and does not occupy much space. However, a code field is more convenient to use in application code development. Unlike numerical identifiers, it is constant. It is also more human-readable.
This is a redundancy. However, it is very convenient since the rest of the data can be modified freely without touching either the application or the database structure. This solves the problem of linking data with logic (each record has a specific meaning in the application and often needs to be treated differently) and the data presentation (codes are easier to read and more human-friendly than numeric IDs).