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.


  • 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?

  • Consider starting with a Person table that has the common info.
    – Rick James
    Mar 16, 2020 at 4:39

1 Answer 1


Here is one idea that I believe is pretty common:

( 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)

( 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.

  • Guess that is a good start as any. Thanks!
    – Juank
    Mar 16, 2020 at 1:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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