I am creating an application that will manage job interviews and help the employers to choose the job seekers based on a ranking system that's based on some user info. I've created a database that has the following tables:
- UserTable (stores Username, password)
- Role Table (stores the Role ID, name and description. The roles are Admin, manager and Regular user)
- UserInfo Table (it contains info like name, address, phone number, skill set, years of experience, email and a few more)
- I have also table User_Role that joins users with their role.
My problem is the following: I need to have specific info for Regular users and specific info for managers, but they will share some of the same info like name,phone number, address, e-mail.
I think I need to use inheritance but if so I need to have separate table for regular user and another for managers and another one for admins right? so the role table looses its purpose.
Other way that I'm thinking is either to have all the info in the user table, but that will be all confused I think, and the last idea is to have two different tables for the different info, one table for regular user info and another one for manager info.
The question is: Should I create different tables for regular users, managers and admins and use inheritance and delete the role table.
Or should I take another approach?