'Relational databases - best practice for mapping to multiple entities, with different statuses?

I'm creating a relational database where User and Team entities have a many-to-many relationship.

When a User joins a Team, their membership of the group is initially pending, and later goes out of a pending state.

Which of the two following approaches would be the most natural choice in a relational database?

Team objects have a list of Member objects, with the properties isPending (a boolean) and user (a User object).
Team objects have two lists of User objects: members and pendingMembers

I would also like a solution which could potentially scale to hold more fields. For instance, each Team may have a User who is designated as its leader. Should Member have another boolean flag isLeader, or should the Team object have a field iseader, of type User?

many-to-many relational-database

Solution 1:^[1]

Full normal form setup would be:

User
    userid, PK
    username

UserType
    usertype, PK
    usertypename 

Team
    teamid, PK
    teamname

Status (leader or regular)
    statudid, PK
    statusname

UserInTeam
    teamid, FK, Team.teamid
    userid, FK, User.userid
    usertypeid, FK, UserType.usertypeid
    statusid, FK, Status.statusid

This method of setting up the UserInTeam table covers all cases. Ex. a user is a leader in team 1, and a regular user in team 2. Any combination is possible.

If a user can only be a leader or a regular user in all it's assigned teams, you could link the UserType to the User table instead.

Avoid boolean statuses. Setting it up in a separate table allows you to add new values later, and your queries will still work.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Nic3500

'Relational databases - best practice for mapping to multiple entities, with different statuses?

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]