Subtype/supertype for teacher, student and admin as users

Question

Every entity whether it is teacher, student admin or superadmin, they have login credentials and a role.

So I have decided to implement subtype and supertype relation.

Users
------------
id
email
password
roleId

Teachers
-------------
userId -> foreign key as well as primary key
name

Students
--------------
userId
name

School
-------------
userId
name

But school, teacher and students are also related to each other. So teacher or student will have another foreign key school_id.

Should I go with this schema or drop the primary key constraint for userId in student, teacher and school.

Should I give them their own unique primary keys.

If I was going with this approach, I would store all data common to both teachers and students in the user table. I don't really see how a school can be a user — Strawberry, Jul 08 '20 at 06:31
Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Jul 08 '20 at 06:32
@Strawberry Yes, I agree. But `Studets` table will have two foreign keys `userId` which is also primary key and another `schoold` which will be nothing but again `userId`. I am having problem here. — humongo, Jul 08 '20 at 06:33

score 0 · Answer 1 · answered Jul 08 '20 at 07:08

For example (schematically):

CREATE TABLE users ( id PRIMARY KEY,
                     name,
                     email,
                     password,
                     role_id FOREIGN KEY roles(id),
                     school_id FOREIGN KEY schools(id),
                     type ENUM('Teacher', 'Student'),
                     teacher_id FOREIGN KEY users(id), -- NULL for teachers
                     CHECK (CASE WHEN type = 'Teacher'
                                 THEN teacher_id IS NULL
                                 ELSE teacher_id IS NOT NULL
                                 END)
                   );

Tables Teachers and Students not needed.

Subtype/supertype for teacher, student and admin as users

1 Answers1