CODEWITHSUNDEEP
mysqldatabasedesign-patternsdatabase-design
user2335065
user2335065

Reputation: 2547

In a database, how to store text that can have multiple languages?

I am designing a database table. Let's say it's the Color table (Red, Green, Blue) and users will be able to choose from it. But when the user is from another country, I want to display the translation of the fields (eg. in Chinese, 紅, 綠, 藍). I am not sure what is the best and most efficient way to store information like this. I've come up with the following ideas

+----------------------+
| Color                |
+----------------------+
| id   (PK)            |
| Language  (PK)       |
| name                 |
+----------------------+

By making id and language as the primary key I can store and find the name of the color.

Or I can do this:

+----------------------+
| Color                |
+----------------------+
| id   (PK)            |
| English              |
| Chinese              |
+----------------------+

In this way I store the name of the colors in a separate column so each color would correspond to one row only, which seems to be more intuitive.

Or I am thinking should I not store the names of colors in different languages in the database, but store them as String variables in PHP?

I also worry about maintainability of the table as there may be additional colors and languages added to the table in the future.

I would like to hear your suggestions. Thanks!

Upvotes: 3

Views: 759

Answers (3)

Randy
Randy

Reputation: 16677

not a direct answer, but consider that for color, you can just display the actual color and no artificial word to describe it.

There are even out of the box color selectors you could use for this particular purpose.

Upvotes: 0

mmilleruva
mmilleruva

Reputation: 2178

If your actual model is more complex than this, you may want to create a separate table to store the language related information. Sticking with the example, If for each color you had (R,G,B) values i.e. (255,255,2555) which don't change with the language you may just want to have the Color table be all language independent fields, then a new table Color_Lang, which has a foreign key to color and holds all of the fields which are dependent on language.

Upvotes: 1

Amrit
Amrit

Reputation: 2072

if languages will be added to the table in the future, then go with your first approach.

+----------------------+
| Color                |
+----------------------+
| id   (PK)            |
| Language  (PK)       |
| name                 |
+----------------------+

On page load you can detect the user's preferred language, and can make the SQL query accordingly.

Upvotes: 1

Related Questions