Design Translation Lookup Tables in Multilingual Schema
The two common techniques of designing translation lookup tables: design a lookup table for each base table, and design a lookup table for each translated field.
Lookup Table for Each Base Table
There is often a separate lookup table for each base table. The lookup table contains a foreign key reference to records in the base table, and contains the values for each translated field in a separate column.
Assuming a completely dense lookup table, the number of rows in the lookup table for a particular language equals the number of rows in the base table.
The example in the figure below shows each record in the lookup table matching only one row in the base table.
Base Table
| Key | Code | Description | Category_Code | Category |
|---|---|---|---|---|
| 1 | A123 | Bread | D45 | Groceries |
| 2 | B234 | Marmalade | D45 | Groceries |
| 3 | C345 | Milk | D45 | Groceries |
Lookup Table
| Key | Language_Key | Description | Category |
|---|---|---|---|
| 1 | DE | Brot | Lebensmittelgeschaft |
| 1 | IT | Pane | Drogheria |
| 2 | DE | Marmelade | Lebensmittelgeschaft |
| 2 | IT | Marmeleta di agrumi | Drogheria |
| 3 | DE | Milch | Lebensmittelgeschaft |
| 3 | IT | Latte | Drogheria |
Lookup Table for Each Translated Field
The alternative approach to using one lookup table for each base table involves a separate lookup table for each translated field, as shown in the figure below.
Getting the translated value of each field requires a separate join to a lookup table. In practice there is often just one physical table that contains translations for multiple fields. When a single table contains translations for multiple fields, you must place a filter on the lookup table to restrict the data to only those values that are relevant to a particular column in the base table.
Base Table
| Key | Code | Description | Category_Code | Category |
|---|---|---|---|---|
| 1 | A123 | Bread | D45 | Groceries |
| 2 | B234 | Marmalade | D45 | Groceries |
| 3 | C345 | Milk | D45 | Groceries |
Lookup Table
| Field_Key | Value_Key | Language_Key | Translation |
|---|---|---|---|
| Description | A123 | DE | Brot |
| Description | A123 | IT | Pane |
| Description | B234 | DE | Marmelade |
| Description | B234 | IT | Marmeleta di agrumi |
| Description | C345 | DE | Milch |
| Description | C345 | IT | Latte |
| Category | D45 | DE | Lebensmittelgeschaft |
| Category | D45 | IT | Drogheria |