Hello. I am trying to make a php application that will get the data from a mysql database. The database is to store some members information. Something important to know, is that every member, is part of a team, and every team is part of a group. Every team has a team leader and maybe a team helper. Every Group has a group leader.
I am not sure how to go about it in the best way. I have thought about different solutions.
Table Members : member_id, name, group_id, team_id, is_team_leader, is_group_leader, is_team_helper.
Table Members : member_id, name, team_id
Table Groups : group_id, group_leader_id (will be the member_id of a member)
Table Teams: team_id, team_leader_id, team_helper_id
What are you actually using this for? This looks like a homework assignment, as real organizations can't work with such specific and inflexible rules. The lack of context makes it hard to apply common sense to your design.
But some problems with the first example are pretty obvious: you can have two members be marked as group leader for the same group.
The second example seems problematic because it doesn't actually say which team is part of which group, except obliquely by saying that some member is the leader and forcing you to infer the team that way.
Does each team really have to have a leader? This means your UI must handle the bootstrapping problem, which isn't hard but does require extra UI design to create a new team at the same time as its first member.