Database design : Users with multiple role

[Mahesh Kumar]

It is for school management.

I am confused how to design the user table.


First i thought of doing like these.

Student
Basic student info with login
Academic details in other few tables

Teachers table with basic info and login
Other few tables linked with teachers

Parent table with basic info and login
Link the parent to child from other table

Admin table with basic info and login
Accountant table with basic info and login


It seems not good for me.

I decided to do like the following

Users table with basic info.
Login tables with login and roles (student teacher….)

Keep the login id in the users table as a foreign key.

Now separate table for student, teacher and parent. Keep the user id in student, teachers table (again, I am coming up with different tables for each user. Is there any advantage of keeping users table. Or is it completely a bad model?)

This one seems good for me. Is that a right design.

In users table
Id, user first name, last name, dob, gender

In login table
Id username password and role

Should i keep the address, phone no in users table? So i don’t need any extra table for admin and accountants, parents

When I was thinking about address and phone no, I am thinking to collect home address and work address.

When I am thinking about parent table, I want to keep mother and father in different row, I was searching in google and found people using mother and father details in column. I think that is not good.

In general, can a accountant see a phone number of a user? Should we hide those details programmatically?

This is the first time i am making a database design and confused. I dont want to keep everything in single table and expose the info to others. At the same time, I don't want to mess the system with too many unwanted tables.