CODEWITHSUNDEEP
sqldatabasedatabase-design
Depa
Depa

Reputation: 509

Database with (avoidable) multiple n-m relations?

I want to create a relational database that stores users, groups and teams. A group consists of multiple users and users can have multiple groups. You can image the group to be something like a chatgroup. In addition, each group can have multiple teams, but a team is exclusively assigned to one particular group. A user can then join one team in every group. However, he is not forced to be in a team, but can also be a member of a group without joining a team. For the sake of simplicity, lets say i have the following tables:

+--------------+
|     User     |
+--------------+
| id int       |
| name varchar |
+--------------+

+--------------+
|     Team     |
+--------------+
| id int       |
| name varchar |
+--------------+

+--------------+
|     Group    |
+--------------+
| id int       |
| name varchar |
+--------------+

I am not quite sure what's the best way to design the database. It's clear that there should be a n:m relation between Users and Groups, since each user can have multiple groups and vice versa.

The problem is that I am not quite sure how to connect the team table. I could create a n:m relation between user and teams, but the problem here is that a user could then join whatever team exists, even if he is not registered in the group that the team is assigned to. I could in additioncreate an 1:n relation between group and teams, but that seems to be way to complicated and in addition would end in having cycles in my db.

Is there any good way or common practive to deal with such particular cases?

Upvotes: 1

Views: 126

Answers (2)

AzamAbbasi
AzamAbbasi

Reputation: 101

You can make your db as below mentioned erd ERD fro user team and group

Upvotes: 1

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

Notes

[p x]   = predicate  x
(c x.y) = constraint x.y

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

All attributes (columns) NOT NULL

Predicates, Constraints, and Relations

[p 1] User USER_ID exists.

(c 1.1) User is identified by USER_ID.

user (USER_ID)  -- p 1
  PK (USER_ID)  -- c 1.1

[p 2] Group GROUP_ID exists.

(c 2.1) Group is identified by GROUP_ID.

group (GROUP_ID)  -- p 2
   PK (GROUP_ID)  -- c 2.1

[p 3] Group GROUP_ID contains team GROUP_TEAM_NO, named TEAM_NAME.

(c 3.1) For each group, that group may contain more than one team; for each team, that team belongs to exactly one group. Group team is identified by GROUP_ID, GROUP_TEAM_NO.

(c 3.2) For each team name, that team name belongs to exactly one team.

(c 3.3) If a group contains a team, then that group must exist.

group_team (GROUP_ID, GROUP_TEAM_NO, TEAM_NAME)   -- p 3
        PK (GROUP_ID, GROUP_TEAM_NO)              -- c 3.1
        AK (TEAM_NAME)                            -- c 3.2
       FK1 (GROUP_ID) REFERENCES group (GROUP_ID) -- c 3.3

[p 4] User USER_ID is a member of a group GROUP_ID.

(c 4.1) Each user may be member of more than one group; for each group that group may have more than one member.

(c 4.2) If a user is member of a group, then that group must exist.

(c 4.3) If a user is member of a group, then that user must exist.

user_group (USER_ID, GROUP_ID)                    -- p 4
        PK (USER_ID, GROUP_ID)                    -- c 4.1
       FK1 (GROUP_ID) REFERENCES group (GROUP_ID) -- c 4.2
       FK2 (USER_ID)  REFERENCES user  (USER_ID)  -- c 4.3

[p 5] User USER_ID from group GROUP_ID joined a team GROUP_ID, GROUP_TEAM_NO.

(c 5.1) Each user, from a group, may join exactly one team in that group.

(c 5.2) If a user joins a team from a group, than that user must be a member of that group.

(c 5.3) If a user joins a team from a group, than that group must contain that team.

user_team (USER_ID, GROUP_ID, GROUP_TEAM_NO)      -- p 5
       PK (USER_ID, GROUP_ID)                     -- c 5.1

        FK1 (USER_ID, GROUP_ID) REFERENCES
 user_group (USER_ID, GROUP_ID)                   -- c 5.2

        FK2 (GROUP_ID, GROUP_TEAM_NO) REFERENCES
 group_team (GROUP_ID, GROUP_TEAM_NO)             -- c 5.3

Upvotes: 1

Related Questions