How to handle different types of users when loggin in?

Question

In an application I've been building, I have a users table. The users represent a firm's staff. I also implemented a role/permission system. Permissions are assigned to roles (staff, manager, director, etc.), and roles are assigned to users. Permissions prohibit the use of certain actions in the app.

Now, I'm introducing another user type: customer (will also be able to log into the app). The customer fields are different from the user fields, so I can't store the customer information in users table (and create a role for customers).

_{(Normally, just creating a customer role for the customers and store the custumers in the users table would be fine, but since the fields are different, I don't think that's an option.)}

First, I thought about creating a seperate table for the customers, but I'm not sure if that's okay, because when someone tries to log into the app, I have to check two tables (users and customers) for the log-in credentials. What if I introduce a third user type? Check three tables? That doesn't seem practical/efficient.

So I thought about seperating the log-in credentials from users, and ended up with three tables:

• users will hold log-in credentials
• staff will hold staff meta/profile information
• customers will hold customer information (same as staff)

This way, I can introduce many different types of users. And if I know what I'm looking for, I can get the record. For example, say I want to get/query a staff, I can just do:

SELECT * FROM staff
JOIN users USING (user_id);

The problem is how do I query users when I don't know what I'm looking for? I mean, the logged user can be a staff, customer, etc. I need to do something like this:

SELECT * FROM users
JOIN [specific_table_name] USING (user_id);

How do I know which type of user just logged in? I could store the user type (the target table name?) in users, but will it help in a (single) query? I mean, find the user in users (using credentials), and then join the user information from another table (staff, customers, etc.)?

Currently, I'm thinking about doing two queries. First is to get the user (credential) record, and the second is to get user (say, profile) information (using a field type from user record).

Of course I'll be doing this in PHP. For example (not real code):

$email = "someone@example.com";
$user  = get_user($email); // SELECT * FROM users WHERE email = "someone@example.com"

switch ($user["type"]) {
    case "staff":
        $user = get_staff($email); // SELECT * FROM staff JOIN users USING (user_id) WHERE email = "someone@example.com"
        break;
    case "customer":
        $user = get_customer($email);
        break;
    // ...
}

// how it's done doesn't really matter. the thing is "type" needs to be checked to get the corresponding user info

Is this best way to handle this? Is there a way to make the queries in SQL (without resorting to PHP)? Like JOIN after WHERE? Or make two queries in one (save the first query result, and use a column value from the first result as a table name in the second query)?

Mentioned tables:

---

_{I'm still researching, and I found out that what I'm doing with the tables is called (?) "Class Table Inheritance". It seems clever for non-login related entities (when going from child to parent; e.g. staff -> user), but in reverse (parent to child, e.g. user -> staff|customer|etc.), it seems problematic. Since I figure these things as I go, I'm stuck at the moment.}

_{One solution that just (while typing) occured to me is to use different log-in forms/pages specific to user types. The form/page could let me know the user type beforehand, but I rather not use this method. So, single log-in form for all users.}

_{I'm calling users as the base/parent table, and the stuff, customers, etc. as the child tables, because first insert happens at users, and child tables use user_id from users.}

Answer 1

You could create a union and use an alias in the query to define different fields mapping for the same name, if there is no field in one table you just cast the alias for an empty field:

SELECT (staff.name) as name FROM users
left JOIN staff USING (user_id)
UNION
SELECT (customers.first_name) as name FROM users
left JOIN customers USING (user_id);

If the userId is a PK/FK it will be only returned by one table.

Answer 2

I would set all users login info and email to the parent table (customers and staff) and two tables with foreign id key for other data, with users.type as you suggested. It could be done with one query, too and Switch condition is not needed:

SELECT users.email,users.type,staff.field2,customers.field3 from users 
LEFT JOIN staff ON(users.type='staff' and users.id=staff.uid) 
LEFT JOIN customers ON(users.type='customers' AND users.id=customers.uid);