User auth for user groups

Hi all

I have an application with one login form where three groups of users will login: admins, clients and members.

They all have a username and password in common. The other columns (like address, phone, projects…) differ.

I think I now have two options:

A. One table for every group, where the login process searches the tables one by one.

B. A parent table with the username, password, group and child id where the login only checks the parent table.

What do you suggest?

Any other ideas or tutorials on that?



i would suggest option B. it is simply better database design in general imo.

I would not use childID I would use parentID in the child table. And have a one to one relationship with parent table etc.

Ok so it could look like the following:


| tbl_user       |


| id             |

| username       |

| password       |

| group          |

| active         |



| tbl_client     |


| id             |

| pid            |

| company        |

| address        |

| projects       |

| ...            |



| tbl_member     |


| id             |

| pid            |

| name           |

| requests       |

| ...            |


And the sql query would look something like








OR =


But how will my app know if the logged in user is a client or a member?

Sorry haven’t had to use something like that before ;)

You may want to change your database design. Since you got there many user types, why not add a user_type attribute on your tbl_user? :) just a suggestion


If the users can be in more than one type/group you may want to have something like:




-- user_id

-- group_id

:: where it is a many-many relationship


in that way, you can simply just create a conditional statement that will check the user_type

Thanks a lot for the answers so far!

A user only belongs to one group. Sorry, this I forgot to mention.

Ok I will use the to store the group (or corresponding value (1/2/3)), but this doesn’t change the SQL query I mentioned in my last post. It seems a bit “unscalable”. If I want to add other groups, I have to add other WHERE…OR clauses.

Is this a price I have to pay or am I doing something terribly wrong? ;)

If a user belongs to one group, then the foreign key should be in the user table. So it would be like:

tbl_user ::





tbl_group ::



No need for a 3rd table :) … So if you’re trying to login for example, you would be able to know the user type by something like this:

if($user->group_id == 1) echo 'group 1';

for the query part:

SELECT * FROM tbl_user WHERE group_id = :id

you won’t need several OR statement for that :)

Thanks levz, got it :)

The reason I was using separate tables for my groups was because I have many (about 30) columns for my members and ~10 cols for the clients and I didn’t want to mix those up to one unmanagable huge table. What do you think?

Your previous design would be a lot harder to maintain in my opinion … :) What is your application all about?

Although I’m not totally satisfied with the one large table, your solution and arguments do sound reasonable :)

I think I can even omit the tl_group, because I don’t really need a group name and will go with numbers solely.

It is a project where clients can search for models/artists based on characteristics and attributes they provided.

As I was reading through this thread I had a thought to help with the Group info being different for various groups.

tbl_user ::





tbl_group ::


group_name //<-- Chang this to table_name

then use table_name to call the {table} for group specific information. Not sure…just random thoughts :o

Thanks for your input, jkofsky. I think I don’t even need the tbl_group and write the corresponding table name directly to tbl_user:


| tbl_user                |


| id                      |

| username                |

| password                |

| group (e.g. tbl_client) |

| active                  |


What do you think?

One problem, lots of solutions ;)

That works too :D

Just to help with DB schema:

I would put everything that is common to ALL ‘users’ (i.e. username, password, etc.) into the tbl_users. In your case, yes, each user has a tbl_group. Then put fields that each group needs in there own tbl_*.

DB normalization is simular to OOP. A parent object (tbl_user) that has a ‘link’ to a child object (tbl_group1, etc.)

I guess I’m rambling…sorry. Just trying to help you work through the problem :)

Thanks for the thoughts, appreciate it :)

I just found the usergroups extension and will test it tomorrow. Maybe it is exactly what I am looking for.

ycast was just wondering if usergroups worked for you here? Just found it myself and looking for opinions on it.