MySQL Forums
Forum List  »  Newbie

Database design : Users with multiple role
Posted by: Mahesh Kumar
Date: August 16, 2023 04:35AM

It is for school management.

I am confused how to design the user table.

First i thought of doing like these.

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.

Options: ReplyQuote

Written By
Database design : Users with multiple role
August 16, 2023 04:35AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.