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?

Thanks,

ycast

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




SELECT 

   * 

FROM 

   tbl_user, 

   tbl_client, 

   tbl_member 

WHERE 

   tbl_user.id = tbl_client.pid 

OR 

   tbl_user.id = tbl_member.pid

?

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

Edit:

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

tbl_user

tbl_group

tbl_user_group

-- 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 tbl_user.group 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 ::

username

password

email

group_id

tbl_group ::

id

group_name


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 ::

username

password

email

group_id

tbl_group ::

id

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 http://www.yiiframework.com/extension/usergroups/ 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.