Is following way is right Database model for following problem

database-design

I am new to the database design so I have to check my data model is correct way to handle the problem or not.

Problem Statement: I am designing the database for student portal which have following user roles

  1. Administrator
  2. School(Principal)
  3. Teacher
  4. Student

The functionalities is for phase I is just maintain all users data and provide the login system.

I am thinking about the database table are

-------------   -------------   -----------
Schools            Teachers       Students
-------------   -------------   -----------
id                id              id
name              name            name
other info        other info      other info

And I am decided to create the another table which store the login creadintilas and role with id as below

Users Table

--------------------------------------------
username    password      role      user_id
--------------------------------------------

but I am not sure about how to applying the PK/FK relations on the table.
I want create the same login system for all system because all the users shares some features i.e. teachers,student,schools can update the student info.
Questions

  1. Is this right way of thinking?

  2. Can i do it in another better way?

Best Answer

Here are two different possible solutions.

Solution 1: enter image description here

Solution 2: enter image description here

For both models, Users is a parent table, with different tables subclassing it. This is achieved by the primary key of the child tables also being a foreign key to the Users' primary key.

Solution 1 involves having a list of possible user roles in the Roles table (Administrator, School, Teacher, Student) with the User having a reference to a role.

Solution 2 involves deriving the idea of the User's role from which child table the User belongs to. This would mean that a row belonging to Users must also belong to one of the child tables.

I personally like the first solution better, since it separates the idea of a Users' role in the portal from the User's position within the school environment.

As an additional note, I would look into "salted password encryption" for storing passwords, especially if this database is for a real application.

Related Question