MySQL : reference a column from another column

Question

I would like to add a dictionary funtion to my program using MySQL.

It would have 3 tables: products, languages and product_dictionary

The user would choose a product and a language and the corresponding product name would be found in the product_dictionary.

If the products table is:

products_id | product_name
1           | chocolate
2           | flour

and the languages table is:

languages_id | language_name
1            | english
2            | french
3            | german

I have two product_dictionary table ideas but I am not sure which is the best.

products_id | languages_id | translation
1           | 1            | chocolate
1           | 2            | chocolat
1           | 3            | Schokolade
2           | 1            | flour
2           | 2            | farine

Where the product_id and language_id create a compound primary key to find the translation;

or

products_id | english   | french   | german
1           | chocolate | chocolat | Schokolade
2           | flour     | Farine   | Mehl

Where the value of the language_name column from the languages table is the column name in the product_dictionary table and the product_id also being the primary key for the product_dictionary table.

Could someone give me there insight? Thank you.

the first version of product_dictionary is better because it's more extensible - you can add more languages without altering the database schema. — ADyson
– ADyson, Commented May 17, 2017 at 8:39

Stefano Zanini · Accepted Answer · 2017-05-17 08:40:48Z

1

I strongly recommend the first option for a few reasons:

new languages may need to be added, and with option 2 that would require you to change the table strucutre, while with option 1 you'd only need to insert a new row
with option 1 the query you'd have to use in your code can have two parameters in its where clause, while with option two you'd have to build a different select for each language

answered May 17, 2017 at 8:40

Stefano Zanini

5,9362 gold badges16 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mathieu Dauré · Accepted Answer · 2017-05-17 08:42:52Z

I would suggest you to have the following database schema :

products

id_product |
1          |  
2          |  
3          |

languages (same as you)

languages_id | language_name
1            | english
2            | french
3            | german

product_dictionary (the first one)

products_id | languages_id | translation
1           | 1            | chocolate
1           | 2            | chocolat
1           | 3            | Schokolade
2           | 1            | flour
2           | 2            | farine

You can add a PRIMARY_KEY on (products_id, languages_id) in product_dictionary.

This is generaly not a good idea to use a column for each language as it's not easy to maintain, if you add 100+ more languages for example.

Collectives™ on Stack Overflow

MySQL : reference a column from another column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related