(MySQL+PHP) - Users belonging to multiple groups?

[denizengt]

I have a basic database consisting of an Articles table, Users table, and Groups table.

In groups there's a dancers group and musicians group. My users are in only one of these groups, meaning I have a column which assigns a group id to the related group in Groups.

Ok, but I now have users that are both dancers and musicians! And hence need to see the articles related to both groups. How can I associate one user, with two groups? Do I set this up in mysql or do it in PHP? I'm thinking the former, cos there can be any combination of users with multiple groups.

Any help will be really appreciated.

[bwh2]

this should be your basic table setup:

tbl_users
-------------
user_id
user_name
... add other columns. remove group_id column from this table



tbl_groups
-------------
group_id
group_name


tbl_users_groups
-------------
user_id
group_id
tbl_users_groups tells you which users are in which groups.

[denizengt]

Hmm that makes sense when I consider them as objects. Thanks for your help, now to extend my php app to take advantage of that functionality :beam:

[bwh2]

yep. always design for scalability. in the end it'll save you a lot of time.

[denizengt]

Just a further question; how would I associated any given single article with multiple groups?

[bwh2]

tbl_articles
--------------------
article_id
article_text


tbl_articles_groups
--------------------
article_id
group_id

this is in addition to tbl_groups which i already posted.

[denizengt]

Thanks again for this, I did get something similar a bit after my post and it works a treat!

[bwh2]

cool beans. the good ole relational db, gotta love it.