Hello. I have an organization where the members are grouped in teams, which have a team leader, and big groups, which have a group leader.

It goes like this, one member can only have one team, meaning one team leader. One team (team leader) takes care of 1 or more than 1 members. 1 team can have 1 or 2 team helpers.

Each team is part of 1 group. Each group have a group leader. The group leader is in charge of 1 or more than 1 group leaders.

The way i have the tables set up is as follows:

Member Table : member_id, name, group_id, team_id, helper_id
Team Table: team_id, team_leader, team_helper, team_helper2
Groups Table: group_id, group_leader


I dont know if thats the best way to do it, I actually feel its not even close. Does anybody has any input? Thanks!

Hi,

It surely depends on what you want to do with the entities and how you want to relate them with other entities. What kind of statistics do you want to yield from these tables, for example?

With the way things look to me, Member Table has a lot of columns that can be relegated to Team Table. Here is a re-write.

Members : member_id, name
Team_members: team_id, member_id, leader_flag*
Group_teams: group_id, group_leader**, team_id

*The value for leader_flag is either '1', '2', or '0'. They are leader, helper, lay member, respectively.

**The value for group_leader should be found in Members.member_id.

I'd prefer leader_flag over having three columns in your Team Table because this makes it easier to count helpers and lay members in each team. Ah, I am assuming two helpers in a team are peers. If you want to establish a rank between helpers, use one more flag '3' for subordinate helper.