11d0dd9f   
   From: esquel@sommarskog.se   
      
   wackyphill@yahoo.com (wackyphill@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@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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)