Use 5 tables, Teachers
, Students
, Contacts
, TeacherContacts
, and StudentContacts
.
TeacherContacts
is a cross-reference between Teachers
and Contacts
.
StudentContacts
is a cross-reference between Students
and Contacts
.
In SQL Server, I would create this schema as:
USE tempdb;
CREATE TABLE dbo.Students
(
StudentID INT NOT NULL CONSTRAINT PK_Students PRIMARY KEY CLUSTERED IDENTITY(1,1)
, StudentName VARCHAR(255)
);
CREATE TABLE dbo.Teachers
(
TeacherID INT NOT NULL CONSTRAINT PK_Teachers PRIMARY KEY CLUSTERED IDENTITY(1,1)
, TeacherName VARCHAR(255)
);
CREATE TABLE dbo.Contacts
(
ContactID INT NOT NULL CONSTRAINT PK_Contacts PRIMARY KEY CLUSTERED IDENTITY(1,1)
, ContactName VARCHAR(255)
);
CREATE TABLE dbo.TeachersContacts
(
TeacherID INT NOT NULL CONSTRAINT FK_TeacherContacts_TeacherID
FOREIGN KEY REFERENCES dbo.Teachers(TeacherID)
, ContactID INT NOT NULL CONSTRAINT FK_TeacherContacts_ContactID
FOREIGN KEY REFERENCES dbo.Contacts(ContactID)
, CONSTRAINT PK_TeachersContacts PRIMARY KEY CLUSTERED (TeacherID, ContactID)
);
CREATE TABLE dbo.StudentsContacts
(
StudentID INT NOT NULL CONSTRAINT FK_StudentsContacts_StudentID
FOREIGN KEY REFERENCES dbo.Students(StudentID)
, ContactID INT NOT NULL CONSTRAINT FK_StudentsContacts_ContactID
FOREIGN KEY REFERENCES dbo.Contacts(ContactID)
, CONSTRAINT PK_StudentsContacts PRIMARY KEY CLUSTERED (StudentID, ContactID)
);
INSERT INTO dbo.Students (StudentName) VALUES ('Student1');
INSERT INTO dbo.Students (StudentName) VALUES ('Student2');
INSERT INTO dbo.Teachers (TeacherName) VALUES ('Teacher1');
INSERT INTO dbo.Teachers (TeacherName) VALUES ('Teacher2');
INSERT INTO dbo.Contacts (ContactName) VALUES ('Contact1');
INSERT INTO dbo.Contacts (ContactName) VALUES ('Contact2');
INSERT INTO dbo.Contacts (ContactName) VALUES ('Contact3');
INSERT INTO dbo.Contacts (ContactName) VALUES ('Contact4');
INSERT INTO dbo.StudentsContacts (StudentID, ContactID) VALUES (1,1);
INSERT INTO dbo.StudentsContacts (StudentID, ContactID) VALUES (1,3);
INSERT INTO dbo.TeachersContacts (TeacherID, ContactID) VALUES (1,1);
INSERT INTO dbo.TeachersContacts (TeacherID, ContactID) VALUES (1,2);
INSERT INTO dbo.TeachersContacts (TeacherID, ContactID) VALUES (2,3);
INSERT INTO dbo.TeachersContacts (TeacherID, ContactID) VALUES (2,4);
SELECT T.TeacherName, C.ContactName
FROM dbo.Teachers T
INNER JOIN dbo.TeachersContacts TC ON T.TeacherID = TC.TeacherID
INNER JOIN dbo.Contacts C ON TC.ContactID = C.ContactID;
SELECT S.StudentName, C.ContactName
FROM dbo.Students S
INNER JOIN dbo.StudentsContacts SC ON S.StudentID = SC.StudentID
INNER JOIN dbo.Contacts C ON SC.ContactID = C.ContactID;
Contacts can be shared between multiple teachers, multiple students, and both students and teachers. This allows fields to be added to the contacts table, without having to duplicate that data across multiple Contacts
tables. The FOREIGN KEY
constraints prevent orphan rows in both the TeachersContacts
and StudentsContacts
tables. The PRIMARY KEY CLUSTERED
options on the TeacherID
, StudentID
, and other fields, ensures data is stored on disk the order teachers and students are added to the system.
The results of the above queries (for demo purposes) are:
