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
- Administrator
- School(Principal)
- Teacher
- 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
Is this right way of thinking?
Can i do it in another better way?