Hi there. I've been researching and thinking for quite a while now but I'm not getting a solution in terms of models and/or table relationship to represent and manipulate the data the way I need. I thought I could bring this problem to the discussion group since some folks in here probably know how to solve it at the tip of their tongue (or fingers).
So, here's the problem:
I have the following tables/models:
groups (id, name, etc) users (id, name, etc) talents (id, name, etc) events (id, name, etc) groups_users(group_id, user_id) : groups habtm users; users habtm groups - Each group has many users; each user belongs to many groups. talents_users(talent_id, user_id) : talents habtm users; users habtm talents - Each user has many talents; each talent belongs to many users. events_groups(event_id, group_id) - Each group has many events; each event belongs to many groups.
Here's where I get confused as for how to map the extra information I need. I wanted to have a table of roles like the following:
roles (group_id, user_id, talent_id, event_id) - Each group/event (in events_groups) has many user/talent (in talents_users) where user belongs to group.
I don't know if a table like roles is a good solution for this problem and if so what are the necessary changes to the existing models (groups, users, etc) and what new models/etc need to be created (and how) to provide that functionality?
Here are some of the things I would like to do with that:
a) Add/delete talents/users to/from specific group/events. b) List the events associated to a specific group with corresponding talents/users for each group/event. c) List the events a user is associated to for a specific group (include the user/talents for each event). d) List the events a user is associated to (all groups) and include the specific talents. e) etc, etc, etc.
Thanks in advance for your help. Eduardo.