0

I am kind of stuck in an easy modelling / design process. How would you design the following model? I'm working with Entity Framework 5 / Model First.

  • I have Users working in Companies.
  • Each User can have multiple Roles in a Company.
  • Each User can work at multiple Locations for a Company.
  • A User can work at more than one Company, and thus has to choose in which company's name he is acting (this is important).

I am now designing weird looking ternary classes like "UserLocationCompany" which has 1-n-relationships to each entity (what you would do when designing tables in a DB), and properties like "User.CurrentCompany" which ends up having a dead end at the Company entity. What is the right approach for such a problem?

Trevor
  • 51
  • 3

2 Answers2

0

You know better than I do whether this fits your case, but could you make Location a property of the Role? So a Role would link onto either a Location or perhaps one Role can have many Locations.

Either way, this stops you repeating the work of linking Company and User, by using the existing connection to add more data.

Hope that's useful to you :)

Ste Griffiths
  • 318
  • 5
  • 15
  • Unfortunately, this does not work, as "Location" is used throughout the project for other purposes than Role (e.g. a Company has a BillingAddress which is also a (more detailed) Location). – Trevor Jul 17 '13 at 13:48
0

'How would you design the following model' - From the information supplied, this is my thought process. 

    Company --< CompanyLocation >-- Location

    "CompanyLocation"
    PK CompanyLocationId
    FK CompanyId
    FK LocationId

I'm taking a guess that more than one company can share a location, so this this relationship table makes this possible.

    User --< UserCompanyLocation >-- CompanyLocation

    "UserCompanyLocation"
    PK UserCompanyLocationId
    FK UserId
    FK CompanyLocationId
    Primary

This table related the user to the Company in the location - and allows the user to set if it's his primary location.

    Company --< CompanyRole >-- Role

    "CompanyRole"
    PK CompanyRoleId
    FK CompanyId
    FK RoleId

This table shows what companies contain which roles.

    User --< UserCompanyRole >-- CompanyRole

    "UserCompanyRole"
    PK UserCompanyRoleId
    FK UserId
    FK CompanyRoleId

Now the user can be assigned to a specific role in the company. You could also add a 'primary' column if you want a user to be able to assign a primary role.

Does this help at all?

EDIT:

The other option is to remove the UserCompanyRole and UserCompanyLocation tables and merge them in to one UserCompanyRoleLocation table. There are plusses and minuses to doing this; it would cause duplicate location rows for each role and vice-versa - but it would make the joins and queries a bit easier.

    User --< UserCompanyRoleLocation >-- CompanyRole
    User --< UserCompanyRoleLocation >-- CompanyLocation

   "UserCompanyRoleLocation"
   PK UserCompanyRoleLocation
   FK User
   FK CompanyRole
   FK CompanyLocation
   Primary
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Scott Allen
  • 513
  • 2
  • 13
  • This is similar to the approach I took with the "ternary" tables described above, where I try to design the EF Model like a database. I have a feeling though that this is ugly and that there is no need to design after a database - EF Model First should allow me to care about my model and not care about the database. – Trevor Jul 19 '13 at 06:04
  • Are you doing this in model first? May I suggest you design the DB to a fitting spec and then reverse engineer code first? I have struggled to actualise some of my models in Model First and always fell back to doing the DB design and reverse engineering with EF Power Tools. I find this is a faster process too. Check out reverse engineer code first [here](http://msdn.microsoft.com/en-us/data/jj200620.aspx). This should also set your relationships up regardless of junction tables etc. – Malkin Jul 19 '13 at 08:30