CODEWITHSUNDEEP
mysqlsql
NiceToMytyuk
NiceToMytyuk

Reputation: 4277

Multiple foreign keys or multiple tables?

I'm actually stuck on how's better to manage a table creation for a following situation,

let's say i have a table called descriptions and tables like items, types, choices

each item in items or type in types or choice in choices could have one or more description

let's say table items types and choices has the following table structure (each could have specific column for it's table)

+----+------+-------+
| ID | NAME | PRICE |
+----+------+-------+
| 1  | CAR  | 5     |
+----+------+-------+
| 2  | BUS  | 10    |
+----+------+-------+

and table descriptions has the following one:

+----+-------+---------+---------+
| ID | DESC  | DESC_EN | DESC_RU |
+----+-------+---------+---------+
| 1  | CIAO  | HELLO   | ПРИВЕТ  | // This description have to belong to items
+----+-------+---------+---------+
| 2  | PIZZA | PIZZA   | ПИЦЦА   | // This description have to belong to types
+----+-------+---------+---------+

so at this point my doubt is, should i create 3 columns in descriptions should i create 3 foreign key for items types and choices or should i create 3 separate tables for descriptions for each table?

Upvotes: 1

Views: 250

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

An alternative solution to the translation problem is to have a table with one row per id, language, and description.

This is handy, particularly for adding a new language. For a new language, nothing needs to change in the database, other than adding rows to some tables.

Another advantage is that a single table can actually hold the translations for multiple different tables -- so all the translations are in one place. That can simplify keeping them up-to-date and ensuring consistency across an application.

One disadvantage is that the translation column has a single collation. That makes it tricky to customize sort-orders (and sometimes comparisons) across languages. Whether this is an an issue also depends on what languages you envision for your application. Some languages, such as Arabic and Hebrew are written right-to-left which can introduce other complications.

Upvotes: 4

Related Questions