Many to many table design question
- by user169867
Originally I had 2 tables in my DB, [Property] and [Employee].
Each employee can have 1 "Home Property" so the employee table has a HomePropertyID FK field to Property.
Later I needed to model the situation where despite having only 1 "Home Property" the employee did work at or cover for multiple properties.
So I created an [Employee2Property] table that has EmployeeID and PropertyID FK fields to model this many 2 many relationship.
Now I find that I need to create other many-to-many relationships between employees and properties. For example if there are multiple employees that are managers for a property or multiple employees that perform maintenance work at a property, etc.
My questions are:
1) Should I create seperate many-to-many tables for each of these situations or should I just create 1 more table like [PropertyAssociatonType] that lists the types of associations an emploee can have with a property and just add a FK field to [Employee2Property] such a PropertyAssociationTypeID that explains what the association is? I'm curious about the pros/cons or if there's another better way.
2) Am I stupid and going about this all worng?
Thanks for any suggestions :)
