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

wackyphill (AT) yahoo (DOT) com (wackyphill (AT) gmail (DOT) com) writes:
This is getting way too abstract. :-)

Maybe you can being with explaining what a property is. First it sounded
like an abstract term, as when I right-click something in Object Explorer
and select Properties.

But then you say "perform maintenance work at a property". That makes
me think that you actually mean "property" in the concrete sense, a
plot of land somewhere.

If you mean "property" in the abstract sense, already with the design
with two tables, it has a smell of an EAV design. A design that sometimes
is right, but also sometimes is an excuse for modelleing data properly.
Then adding PropertyAssociationType make things even worse.

But I may not at all be understanding what you are talking about.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

I'm going to assume that you mean "property" as in "income-producing
real estate", like rental apartments, shopping malls, houses, etc.

So in that case, you have a bunch of properties, and you have a bunch
of of people who for the company that owns the properties. Sarah may
be the accountant for properties A and C. Mike is the maintentance
man for Properties A, ,F, and G. Jane is the manager for Properties C
and F.

I think you need three basic tables: Property, Employee and Role.
The fourth table Employee_Role_Property would have foreign keys to the
other three tables and resolve the relationship among them.

As far as the primary key for Employee_Role_Property goes, that's a
business question. Can a property have more than one employee perform
the same role? For instance, a large property may have two
maintenance men. Can a single person perform more than one role for a
given property? The answers to these questions will determing the
primary key of your fourth table. Just remember, that your key should
cover at least two of the three columns.

You can get fancier, like having a table that records the _possible_
roles for each person. Then when assigning an employee to a property,
the application user can pick from a list of potential candidates. In
a big operation, this is very important, in a smaller one, it may be
overkill.

Thanks,

Bill

On Mar 14, 12:45*am, bill <billmacle... (AT) gmail (DOT) com> wrote:
Sorry, the term was vague. Yes property as in real estate location
A property can have multiple maintenance men. I believe all three
fields need to make up the Primary Key for Employee_Role_Property. I
can imagine the same employee at a property having multiple roles, so
that would be necessary, correct?

wackyphill (AT) yahoo (DOT) com (wackyphill (AT) gmail (DOT) com) writes:
I think Bill's suggestion is quite on target, and, yes, it seems that
all three columns should be the PK.

One thing to consider is that if there are two people in the same role
for the same property, one of them may be the main responsible, which
could call for a "ismainreponsible" column, together with a unique
filtered index(*) on "(role, property) where is mainresponsible = 1".

But this may not at all be the case. I only mention this, because when
I have encountered such relation there has often been an "isdefault" or
"ismain" flag.


(*) Filtered indexes is a new feature in SQL 2008, and for SQL 2005 you
would have to code the same condition in some different (and more
kludgy) way.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

I like the idea Erland. I wish I could use 2008 for this but must use
2005 for now. There are a few types where yes, there is a primary
(MainResponsible). What I have been doing is modeling that as a
Foreign Key field of the Property table. So for example there is
technically only 1 real Property manager so there is a
PrimaryManagerID field in the property table.

However there are situations where other people need to act as
property manager for that property in order to provide coverage, etc.
So that's how this Many2Many table came into being. Do you think this
is a bad thing to do?

The problem with the way I'm doing it is I need to decide if the
Many2Many table should include the primary person as well as the non-
primary people or just the non-primary people.

What do you think?

wackyphill (AT) yahoo (DOT) com (wackyphill (AT) gmail (DOT) com) writes:
No, it sounds right.

I think you should have everyone there. Then you can set up an indexed
view over the primary onces. The point here is that the indexed view
can serve to enforce that there can only be one main for each role.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

OK, I understand.

Thanks so much for sharing your expertise!

OK, I understand.

Thanks so much for sharing your expertise!