Trouble with Many to Many relationship

Question

question

Frank 1 asked Jun 30, '10

Trouble with Many to Many relationship

Hi everyone, I'm trying to sort out my database before developing my interface any further and have run into a problem. Many to many relationships. I understand the theory but am falling short on putting it to practice for SQL tables.

The database is for a university.
I have 2 tables: a modules table and a lecturers table.
Each module has a moduleID and each lecturer has a lecturerID.
A module can have multiple lecturers and a lecturer can have multiple modules.
How can I easily implement this?

Do I create a third table i.e module_staff with its own uniqueID i.e module_staffID and include the moduleID and lecturerID as foreign keys?
If I do this, and put moduleID in the second column and lecturerID in the third, do I simply allow the moduleID to be repeated when there are more than one lecturer ID associated with it?

Any quick help with this would be greatly appreciated.

Thanks

sql-server-2005 foreign-key identity normalization

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-06-30T23:37:58Z

Jay D answered Jun 30, '10

Your on the right track. Creating another table allowing the joining of modules with lecturers would provide the structure you require.

For Example...

USE DemoDb;            
GO            
            
CREATE TABLE Modules            
(            
     ModuleId   INT NOT NULL IDENTITY(1,1)            
   , Name       VARCHAR(25) NOT NULL            
   , LastModDt  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP              
   , CONSTRAINT PK_Modules_ModuleId            
   PRIMARY KEY CLUSTERED (ModuleId)            
   WITH (IGNORE_DUP_KEY = OFF)            
)            
            
CREATE TABLE Lecturers            
(            
      LecturerId    INT NOT NULL IDENTITY (1,1)            
    , Name      VARCHAR(25) NOT NULL            
    , LastModDt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP            
    , CONSTRAINT PK_Lecturers_LecturerId            
    PRIMARY KEY CLUSTERED (LecturerId)            
    WITH (IGNORE_DUP_KEY = OFF)            
            
)            
            
-- PK for this table consists of both             
-- LecturerId and ModuleId            
CREATE TABLE LecturerModules            
(            
     LecturerId INT NOT NULL            
     CONSTRAINT FK_LecturerModules_LectureId            
     REFERENCES Lecturers(LecturerId)            
   , ModuleId   INT NOT NULL            
     CONSTRAINT FK_LecturerModules_ModuleId            
     REFERENCES Modules(ModuleId)            
   , LastModDt  DATETIME NOT NULL   DEFAULT CURRENT_TIMESTAMP            
   , CONSTRAINT PK_LecturerModules            
     PRIMARY KEY CLUSTERED (LecturerId, ModuleId)            
     WITH (IGNORE_DUP_KEY = OFF)            
)            
            
-- Dummy some data            
INSERT INTO Modules (Name) VALUES ('History 101')            
                                , ('English 101')            
                                , ('State Politics')            
                                , ('Modern History')            
                                , ('Intro to Art')            
            
INSERT INTO Lecturers (Name) VALUES ('Albert Einstein')            
                           , ('Issac Newton')            
                           , ('Alexander Bell')            
                           , ('Ben Franklin')            
                           , ('Some Guy')            
            
INSERT INTO LecturerModules (ModuleId, LecturerId)            
                             VALUES(1,4)            
                                 , (1,5)            
                                 , (2,2)            
                                 , (3,1)            
                                 , (5,2)            
                                 , (4,4)            
                                 , (4,2)            
            
--Sample Query to return Modules            
--with Lecturers            
SELECT m.Name AS ModuleName, l.Name AS LecturerName            
FROM Modules AS m INNER JOIN  LecturerModules AS lm            
ON m.ModuleId = lm.ModuleId             
 INNER JOIN Lecturers AS l ON l.LecturerId = lm.LecturerId            
GROUP BY m.Name, l.Name            
ORDER BY m.Name

Produces the following result...

ModuleName                LecturerName            
------------------------- -------------------------            
English 101               Issac Newton            
History 101               Ben Franklin            
History 101               Some Guy            
Intro to Art              Issac Newton            
Modern History            Ben Franklin            
Modern History            Issac Newton            
State Politics            Albert Einstein            
            
(7 row(s) affected)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question