3

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!

user2335065
  • 2,337
  • 3
  • 31
  • 54
  • Possible duplicate of [Schema for a multilanguage database](https://stackoverflow.com/questions/316780/schema-for-a-multilanguage-database) – EstevaoLuis May 31 '17 at 09:31

3 Answers3

1

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.

Amrit
  • 1,964
  • 19
  • 25
1

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.

mmilleruva
  • 2,110
  • 18
  • 20
0

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.

Randy
  • 16,480
  • 1
  • 37
  • 55