CODEWITHSUNDEEP
mysqldatabase
njun
njun

Reputation: 135

MySQL : reference a column from another column

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.

Upvotes: 0

Views: 239

Answers (2)

Mathieu Dauré
Mathieu Dauré

Reputation: 89

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.

Upvotes: 1

Stefano Zanini
Stefano Zanini

Reputation: 5916

I strongly recommend the first option for a few reasons:

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

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

Upvotes: 1

Related Questions