0

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?

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

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.

| improve this answer | |
  • Guess that is a good start as any. Thanks! – Juank Mar 16 at 1:10

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.