Using a seperate table to 'join' make a table relation

In the past I have always made the relationship between two tables using foriegn keys. So, to link employee to employer I would have a column in employee table called employer_id so that one employee could have one employer and one employer may have many employees. Pretty textbook stuff really but what if one employee had two jobs (employers)?? For this I may create the following tables:

employee
employer_employer_join
employer

The table employer_employer_join would have two columns that would make the join, employer_id and employee_id, and I would have no need for employer_id in the employee table. This way I could create many relations between the two and not fixed to single relationships for either.

I adopted this technique in my last project and it worked for any join I had to make but it was more work and more tables to manage. Is this a recommeded method of implementing joins? Would I only use this method when there are multiple rows on either side (many employees for one employer but also many employers for one employee) or if some tables use it, it makes more sense to make all joins in a project this way to keep the design theory consistant? Should I carefully deptermine the tables that dont require this and just implement foriegn keys in those tables (eg. A car only even has at any one time one colour)? And finally, does this technique have a name that I can learn more about??

Hopefully the above is clear, Im newish to building large database designs but I understand the importance of getting this part correct from the start. Any help would be much appreciated. Thanks

Burnsy Received on Thu Jan 10 2008 - 11:10:13 CET