database design and EF model with various User Roles RRS feed

  • General discussion

  • I need some guidance for a database design around a project management system. My question is mainly around how should I design the columns or tables for functionality around the various Roles.

    The system will have several users in a Users table and the following roles in Roles table (Company Adminis, Project Manager, Supervisor, Alternate Supervisor, Employee)

    The various entities involved are Users, Roles, UserRoleMapping, Company, Projects

    • A company has many Projects and has one ore more Company Administrators
    • Every Project has a Project Manager
    • Every Project has several Employees assigned to it
    • Each Employee has a Supervisor

    I am looking for a good db design that would translate well to an Entity Framework model.

    This is the approach I have thought of and would appreciate any feedback: Use default ASP.NET Membership that would provide Users, Roles, UserRoleMapping functionality.

    Please see the image below. Thinking of using a Table per Hierarchy to store the various managers (Company Admins for a company, Project Manager for a project, Supervisor for an employee) in the same table. What do you think of this as the database design and please suggest how this would work for the Entity Model?

    Also, on another note - do you think the way I have connected Project, Tasks and Employees is correct?

    • Changed type Allen_MSDN Friday, July 27, 2012 6:37 AM
    Wednesday, July 25, 2012 4:12 PM

All replies

    This is not an EF issue. This is a database design issue and you should
    be seeking help from a database forum in how to implement role based
    security via a database and what elements need to be in the database. EF
    is only a virtual implementation of what you have designed at the
    database level.
    Thursday, July 26, 2012 4:42 AM
  • Hi Kothari Pratik,

    Welcome to MSDN Forum.

    I agree with @darnold924, you'd better design your database based on your business, not based on how convenience for EF. I'm not sure what're the properties in your 'CompanyAdmin', 'ProjectManager' and 'Supervisor'. If you do require polymorphic associations or queries, and subclasses declare relatively few properties (particularly if the main difference between subclasses is in their behavior), lean toward TPH. Your goal is to minimize the number of nullable columns and to convince yourself (and your DBA) that a denormalized schema won’t create problems in the long run.

    I will change the type of this thread to discussion, this may attract more members to discuss the design. : )

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Edited by Allen_MSDN Friday, July 27, 2012 6:37 AM
    Friday, July 27, 2012 6:36 AM