Avoid using Many to Many Relationship

I have a 3 tables, Employee, Languages, Employee Languages.

Now, Employee table is a profile table. Nothing special(Id, first name, last name etc.).

Languages table has two columns: Language ID column and Language name column. This table, basically is a list of all languages.

Employee Languages consists of three columns: Employee ID, Language ID and Proficiency.

So, when I want to show employee languages, I have to get language id, then correlate that id to language name and then show it to the end user.

I’m currently thinking about new scenario with two tables:

First would be Employee table.

Second would be Employee Languages, which consists of Employee ID, Language Name and Proficiency.

So, Language name and Proficiency would be populated in front end and would be shown as a drop down list.

My question is which scenario would be much more effective in my case?

Your 2 table design approach can be implemented using a view. This would not compromise your underlying database design based on 3 tables.

Views are under used but often give the best of all worlds.

Expanded:

From your “normalised” 3 table design, which looks OK, you will CREATE VIEW (SELECT Employee ID, Language Name, Proficiency FROM Employee, Languages, Employee_Languages WHERE etc. ). Your application then selects from the view.

3 tables is best for the following reasons.

1. You have a lookup table, pulling data from the lookup table is faster.
2. You can Expand the employee table without worrying the effects on the language table
3. You can Expand the language table without worrying the effects on the employee table.

You can apply a two column key on the lookup table, making it impossible for employee John Doe to learn English twice.

First solution (3 tables) allow you to search of employees with given language in efficient and unambiguous way. The same language name is assigned to different employees (without literal errors like “english”/”Engliish”). It’s a better way for developing new functionality in the future.

Your question is less about whether to use many-to-many relationships and more about the choice between a surrogate or natural key.

You do need three tables – Employees, Languages and Employee_Languages – but you could choose to use the Language Name as a natural key instead of Language ID as a surrogate key.

The choice of whether to use a natural or foreign key is a pragamatic one made of the basis of familiarity, irreducibility, stability and simplicity. Your question indicates that use of a natural key may be the best for your circumstances. You should analyze all the uses of Languages within your application and decide, given the criteria above, whether the use of a natural or surrogate key would be the best for you.