Prev: MSSQL 2005, TSQL, Keywords and variables, Case sensitive or not?
Next: Replicating from SQL 2000 to SQL 2008
From: wackyphill on 13 Mar 2010 15:28
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 :)
From: Erland Sommarskog on 13 Mar 2010 18:02
wackyphill(a)yahoo.com (wackyphill(a)gmail.com) writes:
> 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?

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

From: bill on 14 Mar 2010 00:45
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

From: wackyphill on 15 Mar 2010 08:07
On Mar 14, 12:45 am, bill <billmacle...(a)gmail.com> wrote:
> 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

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?

From: Erland Sommarskog on 15 Mar 2010 18:03
wackyphill(a)yahoo.com (wackyphill(a)gmail.com) writes:
> 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?

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: MSSQL 2005, TSQL, Keywords and variables, Case sensitive or not?
Next: Replicating from SQL 2000 to SQL 2008