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

avatar image

Frank 1
5 2 2 3

(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

avatar image

Jay D
128 2 3 6

(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.

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:

x2016
x39
x35
x16

asked: Jun 30, 2010 at 09:32 PM

Seen: 1386 times

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

Copyright 2016 Redgate Software. Privacy Policy