Many of the many table design issues

Question

Initially, I had two tables in my database, [Property] and [Employee].

Each employee can have one "Home property", therefore the employee table has the FK HomePropertyID field in Property.

Later, I needed to simulate a situation where, despite having only one “home property”, an employee worked or covered several properties.

So, I created a table [Employee2Property], which has the EmployeeID and PropertyID FK fields to model many-to-many relationships.

Now I find that I need to create other many-to-many relationships between employees and properties. For example, if there are several employees who are managers for a property or several employees who perform maintenance work on a property, etc.

My questions:

• Should I create separate many-to-many tables for each of these situations, or should I just create another table, for example [PropertyAssociatonType], which lists the types of associations that an employee can have with a property and simply add the FK field to [Employee2Property], for example PropertyAssociationTypeID, which explains what an association is? I'm curious about the pros / cons or if there is an even better way.
• Am I stupid and is all wrong?

Thanks for any suggestions :)

Answer 1

. :

"" M: N:

• ,
• , .. .
• .

, .

Answer 2

.

• ? , , - . , , B ( FK AssociationType)

• ? , , . , .

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

, "" ..

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

Thus, the same employee can serve multiple roles in the same property. This structure will not work in situations where you need to guarantee that there is one and only any element (for example, HomeProperty), but will allow you to expand the list of roles that an employee could have regarding this property.