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

bissatch_at_yahoo.co.uk wrote:

> Hi,
>
> 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)

Yes. That is exactly how one represents a many:many relationship.

  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?

No. The data model should accurately reflect the business rules. If the rules state 1:many, then the data model should reflect 1:many.

  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)?

I recommend due care for all tasks.

  And finally, does this technique have a name that I can
> learn more about??

Join table. Association table. Probably others too.

> 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

Have you read anything on normalization? If not, I highly recommend doing so. Received on Thu Jan 10 2008 - 16:15:44 CET