How to Work With Dictionary Data in a Database

In dictionary tables, you often encounter the following column pattern:

• An artificial primary key (e.g., id of type integer).
• An artificial alternative key (e.g., code of type char(x)).
• Real data (e.g., name of type varchar(y)).

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 description field.

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).