1

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.

  • 1
    I teach at a University. One of the other professors audited my class. How would you handle that? – Neil McGuigan Jul 4 '14 at 18:34
  • 2
    Looking at the variance of the answers so far, you really should state what requirements you have for the relationships: Do you want/need to reuse a contact entry for multiple professors and/or students? Do you need to be able to enter multiple contact entries for a single professor or student? Can a student be also a professor and if so, should (s)he have different contact entry for both of the roles? – zagrimsan Jul 7 '14 at 9:48
  • @zagrimsan I did not think of all this before you asked. The only thing I was thinking of is that a persone (student or professor) should have multiple contact entries. – codiac Jul 8 '14 at 9:50
  • 1
    Ok, in that case it would seem to me that Max's answer is pretty close to what you need. It doesn't enforce "a contact can not be shared between Student and Professor unless the Student is also Professor" but if that is strictly needed it could be done in application layer. – zagrimsan Jul 10 '14 at 7:19
5

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!

  • Thanks for your replay. I really liked the simplicity and efficiency of your idea. After I thought about it and started implementing it it seemd to me that this scheme would not allow for more than 1 Contact to a Student or Teacher. Is that right? – codiac Jul 4 '14 at 14:44
  • 1
    That is correct. I understood the relationship between Student and Contact to be "A student is a contact". If a contact can have more than one telephone, or more than one address, or more than one email address, these relationships need to be modeled differently than I did. – Walter Mitty Jul 4 '14 at 15:53
  • Having ContactID as the primary key for Professors and Students sounds a bit weird (probably both of them would need to have their own primary keys in any real world scenario), but it really depends on what the actual requirements of OP are... – zagrimsan Jul 7 '14 at 11:55
  • Shared primary key looks weird to me as well. But it has a lot to recommend it, even in the real world. – Walter Mitty Jul 7 '14 at 12:44
0

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.

  • Hi @Marco, once you have a tiny amount of privilege on this site you will be able to add comments to questions, and other answers. – Max Vernon Jul 4 '14 at 14:02
0

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:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.