Database design for this given scenario

Question

I am working on an application that will have 2 kinds of users, teachers and students.

Both the teachers and students have a first and last name, an unique email and an unique username (by unique i also mean that a teacher can't have the same username/email as a student).

Besides these common fields students will have 4 more fields: A, B, C and D.

Teachers will have E, F and G.

What's the best way do design a database for this? Should I use 2 tables (one for students, one for teachers, each with all the fields) or 3 tables(an user table - for the common fields like username and email, and then have a one-to-one relationship with the students and teachers tables that only contain particular fields.

This database will be used in a system where students/teachers will have to login. I guess it's better to look for credentials in just one table, than to have 2 different login points in the application, each querying a different table.

Actually I'll be using hibernate to generate the tables from java classes, so from an object-oriented point of view using 3 tables sounds better.

I'm looking forward to seeing other opinions on this.

Thanks,

Answer 1

CREATE TABLE People
(
    PersonID      INTEGER NOT NULL PRIMARY KEY, -- Add local incantation for auto-allocated numbers
    FirstName     VARCHAR(32) NOT NULL,
    LastName      VARCHAR(32) NOT NULL,
    Email         VARCHAR(64) NOT NULL UNIQUE,
    UserName      VARCHAR(16) NOT NULL UNIQUE,
    PersonType    CHAR(1) NOT NULL CHECK(PersonType IN('S', 'T')) -- S student, T teacher
);

CREATE TABLE Student
(
    PersonID      INTEGER NOT NULL REFERENCES People,
    A             ...,
    B             ...,
    C             ...,
    D             ...
);

CREATE TABLE Teacher
(
    PersonID      INTEGER NOT NULL REFERENCES People,
    E             ...,
    F             ...,
    G             ...
);

There is a constraint that you'd like enforced that the Teacher.PersonID column references a row in People where PersonType = 'T', and similarly for Student.PersonID referencing a row in People where PersonType = 'S'.

There isn't a particularly clean way to enforce that automatically. Your options include adding a column PersonType to each of Teacher and Student that will always hold 'T' or 'S' and then creating a foreign key that references People(PersonID, PersonType). This is ugly because the value in the Teacher or Student table is constant, and because the PersonID alone is unique.

Otherwise, you enforce the constraints in the code. I'd probably use the code backed up by a periodic check for entries that violate the constraint (entries in Teacher that identify a student in the People table, or entries in Student that identify a teach in the People table).

How well this maps to Hibernate is a separate issue. You might find that two views are useful:

CREATE VIEW StudentInfo AS
    SELECT P.*, S.A, S.B, SS.C, S.D
      FROM People AS P
      JOIN Student AS S
        ON P.PersonID = S.PersonID AND P.PersonType = 'S';

CREATE VIEW TeacherInfo AS
    SELECT P.*, T.E T.F, T.G
      FROM People AS P
      JOIN Teacher AS T
        ON P.PersonID = T.PersonID AND P.PersonType = 'T';

Of course, you can argue cogently that the restrictions on PersonType are unnecessary.