Designing tables for school management

[Mahesh Kumar]

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

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

In general, can a accountant see a phone number of a user? Should be 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 expose the info to others. At the same time, just for phone number and address should i create separate table for admin, accountants and parents?

Please help me with user design system