ycast
(Mail2)
March 23, 2011, 8:47am
1
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
keex
(Megrym)
March 23, 2011, 9:51am
2
i would suggest option B. it is simply better database design in general imo.
ycast:
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 not use childID I would use parentID in the child table. And have a one to one relationship with parent table etc.
ycast
(Mail2)
March 23, 2011, 3:07pm
4
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
prozn7
(Verge Gfx)
March 23, 2011, 3:41pm
5
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
ycast
(Mail2)
March 23, 2011, 4:15pm
6
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?
prozn7
(Verge Gfx)
March 23, 2011, 4:24pm
7
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 …
ycast
(Mail2)
March 23, 2011, 4:47pm
8
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?
prozn7
(Verge Gfx)
March 23, 2011, 4:54pm
9
Your previous design would be a lot harder to maintain in my opinion … What is your application all about?
ycast
(Mail2)
March 23, 2011, 5:54pm
10
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.
jkofsky
(Jkofsky)
March 23, 2011, 6:54pm
11
ycast:
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
ycast
(Mail2)
March 24, 2011, 10:15am
12
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
jkofsky
(Jkofsky)
March 24, 2011, 4:49pm
13
ycast:
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
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
ycast
(Mail2)
March 24, 2011, 9:35pm
14
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.