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?

  • ManagerInfo table. – Robert Harvey Jun 9 '16 at 16:53
  • @RobertHarvey you're suggesting to create a seperate table for Manager info? – LikeIfYouCaredAboutMyName Jun 9 '16 at 16:55
  • Are the roles unique? Can a manager be admin as well? or a user be a manager? And is there going to be any need to add more roles in the future? – John Kouraklis Jun 9 '16 at 23:33
  • the roles are unique yes and there wont be any more roles in the future – LikeIfYouCaredAboutMyName Jun 10 '16 at 17:34
up vote 0 down vote accepted

What about different table for different type of user. And one table for common information of user.
Then specific table have a reference to basic info rowID

RegularUser { ID, SpecificDataForRegularUser, BasicInfoID}
Manager { ID, SpecificDataForManager, BasicInfoID}

UserBasicInfo { ID, Name, Phone, ...}

Then data structure for different type of user stay separated and clear

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.