Relational Database design best practice

Question

I am a student and I am trying to design a simple database, so if the ideas seem bad please let me know how to improve.

I have three tables in my mind, one for Professors and one for Students. Both of them have Contacts details. I need to know what is the best practice to achieve a relationship between the three.

I see two possibilities: A) Having both Professors and Students tables use an auto-increment field and a supplementary default initialised field (let's say with 1 for Professors and 2 for Students). Then create a key in the third table (Contacts) as a combination of the unique ID and default initialized fields.

B) Having for tables instead of three, with two identical structure tables as ContactStudents and ContactProfessors, each serving for the appropriate table.

From my perspective I would choose the first one since if I would want to add new fields to the Contact table both would benefit from it.

Which one do you think is best and why? Is there another, better way, of achieving this?

EDIT:

After that variety of answers and at zagrimsan's suggestion I add the following requirements: Student can be also a Professor. A person (Student of Professor) can have multiple contacts. A Contact can be reused for multiple Students and Professors (same kind) but not both Student and Professor at same time except the case when the same Student is also a Professor.

Answer 1

Problems involving classes and subclasses (or, if you prefer, types and subtypes) come up all the time in database design. Your situation looks like a case in point.

In your case, here's what I would do, using a technique called Class Table Inheritance combined with a technique called Shared Primary key.

Create three tables: Contacts, Professors, and Students. Use the autonumber feature for the ID field of the Contacts table. Have an ID field in the other two tables, but do not use the autonumber feature. Instead, whenever you insert a new Contact, obtain the ID field just generated for the new contact, and provide that ID field as the value for ID in either Professors or Students. The ID field is declared as the primary key in all three tables. You can even create a Contact that is both a student and a professor, if that makes sense in your situation.

You place other attributes in the appropriate table, according to the attribute, as you have suggested.

ID is guaranteed to be unique in all three tables. If you want data about only students, join Contacts and Students matching on the ID field. Likewise for data about professors.

Using shared primary key in this way enforces the 1:1 nature of the relationships between Students or Professors and Contacts.

Good luck!

Answer 2

Option B seems the best based on the information you provided.

Students and Teachers are two different concepts and they have different information, even when it seems they store the same.

But if you provide more details what fields/data you are going to store in each table then we can give you a more clear answer.

Answer 3

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: