Perform if in mysql or php

Question

I have 3 tables :

-User

-SuperUser

-SpecialUser

SpecialUser and SuperUser are extensions of the User table. Let's say that User table has {name, email}, SuperUser has {idUser, superPower} and specialUser has {idUser, hairColor}.

Of course a SpecialUser and a SuperUser have a name and email from the user table. This means that a User can be a SuperUser or a SpecialUser.

My question is how do I perform a query that gets all the info of a user (I don't know before the query if he is a specialUser or SuperUser).

I thought about 2 methods :

-Putting a column "userType" (0 : he is specialUser, 1 : he is superUser) in the user table

With this method, should I do a MySQL query with IF inside ? (What would be the query). Or should I do simple query (getting the user table alone) then in PHP I do a if and query the right table (super or special table)

OR

-Not putting any column and doing a MySQL query with 2 joins on the id of the user (technically one of the 2 joins won't return anything)

Which one should be used ? (I care about speed performance, less about memory - Let's say that the tables have over 1 million rows)

Answer 1

How I would do this is in pseudo-sql:

Select * from user
left outer join superuser
left outer join specialuser

And return everything. If superuser's fields are not null, then in the PHP you can operate on that, same for specialuser. And this gives you two advantages:

1) You don't need a field to say what kind of user the user is anymore, the contents of the join will tell you.

2) You can have a user be a superuser and a specialuser at once, if you wished. (And if you didn't want that to happen, you can prevent it using a constraint or similar)

Answer 2

To do this without branching might be tricky, though I have an idea; since you said memory is not a problem, but performance is.

First off make a column in the User table and call it userType like you suggested and store the contents of the table name followed by the table columns in a parse-able string like:

(SuperUser/SpecialUser), ("idUser, superPower"/"idUser, hairColor")

Example:

"SuperUser, idUser, superPower" //for a SuperUser

In psuedo code:

SELECT `userType` FROM `usersTable` WHERE name = [put var here]
Let the returned value be valRet
parse the valRet into an array... //the first value is the table name, the remaining values are column names
make a second sql query based on the array

Performance wise I believe this is good because there is no branching. However, I'm not sure what kind of performance hit you would take on the string parsing. Try benchmarking it on a few thousand queries to see.

//Also check out the answer to a similar question here: Using an IF Statement in a MySQL SELECT query