x

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

more ▼

asked Jun 30, 2010 at 09:32 PM in Default

Frank 1 gravatar image

Frank 1
5 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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)
more ▼

answered Jun 30, 2010 at 11:37 PM

Jay D gravatar image

Jay D
128 1 1 3

Thanks for your help Jay!

However, after analysing my data I realised there was a lot of unnecessary repition. I have 3 tables, one for Reps, one for Lecturers and one for CommitteeMembers - each with a uniqueID, Name and Email fields. Would I not be better consolidating them all into one Users table with a new field for 'Role' in which they can be specified as Rep, Lecturer or Member? If I do this, how to I implement the many-to-many relationship between Lecturers and Modules?

Thanks again
Jul 01, 2010 at 03:32 PM Frank 1
Frank, you're on the right track but be careful with adding the "Role" field to the users table. What if one person could serve in more than one role? Could I be a lecturer and a committee member? Having a users table with UserId as the PK and then a RolesRelationships table with the UserId and Role would be more appropriate.
Jul 01, 2010 at 08:29 PM Jay D
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1949
x35
x34
x13

asked: Jun 30, 2010 at 09:32 PM

Seen: 1163 times

Last Updated: Jun 30, 2010 at 09:32 PM