Many to many - junction table

Question

I am doing this design. Users can speak one or more languages (limit five). first language is the native and the others are in order of fluency.

user 1 speak 3 languages

    user_id|first language|secondlanguage|thirdlanguage|fourthlanguage| fifthlanguage 
    1        english (1)     franch(2)    portuguese (3)      null           null 

What do you think about this scheme? Sounds a bit strange or it is the correct way to do that?

Answer 1

The answer on this one is that it really depends. Relational purists will say this is a repeating group and this is a terrible design, while the quick-and-dirty crowd will say, "if you're only ever going to support 5 languages, then do it". But, really - how can you be sure you'd only support 5 languages forever and always? And what sorts of queries will you be doing? Imagine you want to find all the people who speak English - your query will be:

select * from single_user_has_spoken_languages where lang1 = @englishID or lang2 = @englishID or... etc

If you want to find people that speak both English and French, then your where clause grows again. And every single query will have to change if you ever add a 6th language.

Consider the alternative:

select distinct UserID from single_user_has_spoken_languages where langID = @englishID

Once you stop and think about how your queries will work, good design begins to become much clearer.