locked
The best way to design M:M relationship RRS feed

  • Question

  • I have two tables permissions and groups of many to many relationship.

    -- Table Groups

    CREATE TABLE [Groups]
    (
     [Group_Id] Int IDENTITY(1,1) NOT NULL,
     [Group_Desc] Varchar(50) NOT NULL
    )
    go

    -- Add keys for table Groups

    ALTER TABLE [Groups] ADD CONSTRAINT [Key1] PRIMARY KEY ([Group_Id])
    go

     

           

    -- Table Permissions

     

    CREATE TABLE [Permissions]
    (
     [Permission_Id] Int IDENTITY(1,1) NOT NULL,
     [Permission_Name] Varchar(50) NOT NULL
    )
    go

    -- Add keys for table Permissions

    ALTER TABLE [Permissions] ADD CONSTRAINT [Key2] PRIMARY KEY ([Permission_Id])
    go


     

    I am confuse how to implement the many to many relationship

    which is better to create a composite primary key of Group_id and Permission_id in a new table

    Or to create a new table & select the columns from the two table using join keyword  .


    • Edited by vanpersie Wednesday, March 21, 2018 5:01 AM
    Wednesday, March 21, 2018 5:00 AM

Answers

All replies