Mysql – Database design: User table and relationships

database-designMySQL

I'm designing a DB for a small school project, but currently struggling with the design of the User table and it's related tables.

Basically:

  • A User can be a Student, a Teacher or an Admin.
  • Students, Teachers and Admins share some common data (name, last_name, login dates) but each type has unique information (Teachers have a professional license; Students have one Carrer associated to their profile, and so on).

So I'm stuck with two designs. The first one is as it follows:

Database design with User table

This aproach allows me to have the unique fields (not listed in the image) in each type table, but the problem lies with the field duplication.

The second approach would be to have a single User table and a second User_data table (instead of the three separated tables in the first design), but I think that would create another set of problems, like having NULL values if the current register is a Student, Teacher or Admin.

Any suggestions on how to improve this design?

Best Answer

Here is one idea that I believe is pretty common:

CREATE TABLE users
( user_id ... not null PRIMARY KEY
, user_name ... not null UNIQUE
, surname ... not null
, ...
, user_type ... not null
,     CHECK (user_type IN ('T','S','A'))
,     UNIQUE (user_type, user_id)
);

CREATE TABLE teachers
( user_id ... not null PRIMARY KEY
, license --- not null UNIQUE
, ...
, user_type ... not null
,     CHECK (user_type = 'T') 
, FOREIGN KEY (user_type, user_id)
      REFERENCES users (user_type, user_id)
);

Similar tables for STUDENTS and ADMINISTRATORS.

To determine the name of a teacher, you join with users

CHECK constraints may not be availible in your version of MySQL, you can achieve the same thing with foreign keys to a domain table

All of the above assumes that - for example - teachers and students are disjoint.

Related Question