Many to many table design question

Question

Originally I had two tables in my DB, [Property] and [Employee].

Each employee can have one "Home Property" so the employee table has a HomePropertyID FK field to Property.

Later I needed to model the situation where despite having only one "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-to-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 separate many-to-many tables for each of these situations or should I just create one more table like [PropertyAssociatonType] that lists the types of associations an employee can have with a property and just add a FK field to [Employee2Property] such as 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 wrong?

Thanks for any suggestions :)

Answer 1

This is a very valid question. And the answer is: it depends

The following things suggest using a single 'typed' M:N relationship:

• you often want to process all employee-property relationships, independent of type
• the number of associations is changing all the time, i.e. new types get invented.
• a employee property relationship sometimes changes its type.

If these statements are more wrong then right, you might better be of using separate relationships.

Answer 2

Two considerations should guide your choice.

1. How static will the list of potential types of associations be? You are already having to add new ones, so it seems that the answer here may be - not very. If there is likelihood that this list will grow, stick with choice B (one table with an additonal FK to an AssociationType table)

2. What are the access patterns for this data likely to be? If there will be a need to access the individual types of associations, isolated from all the other types, then multiple tables might be better. But I would only do this is the list of associatipon types was also very static

Answer 3

Create Table Employee
(
    Id int not null Primary Key
    , ....
)
Create Table Property
(
    Id int not null Primary Key
    , ....
)
Create Table Role
(
    Name varchar(10) not null Primary Key
    , ....
)

Into the roles table you would put things like "Manager" etc.

Create Table PropertyEmployeeRoles
(
    PropertyId int not null
    , EmployeeId int not null
    , RoleName varchar(10) not null
    , Constraint FK_PropertyEmployeeRoles_Properties
        Foreign Key( PropertyId )
        References dbo.Properties( Id )
    , Constraint FK_PropertyEmployeeRoles_Employees
        Foreign Key( EmployeeId )
        References dbo.Employees( Id )
    , Constraint FK_PropertyEmployeeRoles_Roles
        Foreign Key( RoleName )
        References dbo.Roles( Name )
    , Constraint UK_PropertyEmployeeRoles Unique ( PropertyId, EmployeeId, RoleName )
)

In this way, the same employee could serve multiple roles on the same property. This structure would not work for situations where you needed to guarantee that there was one and only of some item (e.g. HomeProperty) but would allow you to expand the list of roles that an employee could have with respect to a given property.