I need to design a database that has multiple users, with multiple roles. For instance, a user can be an admin, company, employee, or customer. Each user can have any number of these roles. Right now I have tables that look like this: Database Design

Right now, it seems as though when a user logs in, we will have to return from the "What role(s) is/are the user?" query in order to get the information out of the correct table (ex. admins, employees, customers, or companies). I would like to be able to query one time and grab all of a users information. Is this possible, if so how could it be done?

To sum up the problem: Notice that their are two independent sections (not connected by any lines to the other section). I need a way to connect those.

  • Do you have any queries you've tried so far to get this info? – LowlyDBA Jan 26 '15 at 15:27
  • No, because I cannot see how it would be possible. It's not the syntax that I am worried about, it is more the theory behind it. – Trevor Hutto Jan 26 '15 at 15:54
  • Your sections are connected, but your diagramming tool doesn't see it because you didn't define the referential integrity in your DDL (or your modeling tool) properly. Note that every specialty role has a key of user_id. This should be FK to Users.id. In other words, Users is a super-type and admins, employees, customers, etc. are all subtypes related 1:0,1 to the super-type. – Joel Brown Jan 27 '15 at 0:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.