Multilingual Database table design

0
votes

I am using the following approach for a multilingual database, where the fields I am using such as occupation, eye_color, countries_visited, music_liked, etc., are pre-determined options from lists that have already been translated in different languages. User can only choose from the options presented. For example: Table 1 has all the basic non translatable information. Table 2 has the translation values for eye color and Table 3 has the translation values for Type of Music user likes:

TABLE1: (userlist) 
USERID| FNAME| EYECOLOR_ID| MUSICLIKE_ID
"100", "JOHN", "1", "1,3,5"

TABLE2:(eyevalues)
EYECOLOR_ID| EYE_ENGLISH| EYE_SPANISH| EYE_FRENCH
"1", "BLUE", ÄZUL", "BLEU"
ETC...

TABLE3:(musicvalues)
MUSICLIKE_ID| MUSIC_ENGLISH| MUSIC_SPANISH| MUSIC_FRENCH
"1","ROCK MUSIC", "MUSICA ROCK" "MUSIQUE ROCK"
"2","LATIN  MUSIC", "MUSICA LATINA" "MUSIQUE LATIN"
"3","POP MUSIC", "MUSICA POP" "MUSIQUE POP"

ETC...

By doing a left join query I can pull the correct translation easily for eye color because there is only 1 value for eye color. But how do I present the different values for MUSICLIKE? Is there a better approach to what I am trying to do?

I hope the question is clear....

1
sqldatabase-designmultilingualnested-query
you need to normalise the music like fk in user list using a junction table for the m:n relationship and not store it as a 'list'. – T I
I am sorry, I am just an amateur... do you mean by normalizing music, creating an additional table ? – Juan

1 Answers

0
votes

In the MUSICLIKE_ID column of the USERS table you have what appears to be a list of IDs, this does not meet normal form and is horrible to work with in the long run. You should remove this.

Instead implement a many to many relationship such as 'many users like many music genres' using a junction table (aka bridge/map tables).

In this case the table would be USER_MUSIC ( user_id , music_id ). n.b. you would create a unique constraint covering both columns.

Then when you come to joining the tables you go via the junction table.

select u.user_name, m.music_english
from users as u
join user_music as um
on u.user_id = um.user_id
join music as m
on um.music_id = m.music_id