Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009

    Many to Many between 3 tables

    Hi All,

    I have a problem/don't know the solution... and it is

    User has many Projects, Project has many users, and user has multiple roles for a Project.


    Ram, John are multi skilled persons.

    Ram and John are associated with many Projects in a company

    Project A has many Users (Ram, Shyam, John, Ryan)

    In Project A, Ram takes the roles as 'Configuration Manager and Network Manager' and John takes the roles as 'Developer and System Administer'.

    In Project B, Ram takes roles of 'Developer and System Administer' and Ryan takes roles as 'Configuration Manager and Network Manager'.

    In Project C, all users are developers.

    So we have three tables User, Project and Role

    Users are Ram, Shyam, John and Ryan
    Projects are Project A, Project B and Project C
    Roles are Developer, System Administrator, Configuration manager and Network Manager.

    How do we design this situation.


  2. #2
    Join Date
    Sep 2002
    With a 4th table: UserProjectRoles (UserId, ProjectId, RoleId), with all 2 columns being part of the primary key.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts