How can I separate out multiple values from a column via another table?

Question

I have a table named Team. Each record can have more than one member. Rather than jamming members comma separated value, I am hoping to use separate table to meet the requirement. Id is primary key.

----------------------------
| Id  | Name  | Member
----------------------------
| 1   | TeamA | Jim, Jack
----------------------------
| 2   | TeamB | Micah

I thought of creating a table named User. UserId is the PK and TeamId is FK.

--------------------------
| UserId  | Name  | TeamId
--------------------------
| 123     | Jim   |  1 
--------------------------
| 456     | Jack  |  1
--------------------------
| 789     | Micah |  2

Then I want parent table Team be able to refer Jim and Jack from its child table but I am not sure how to represent it in one to many relationship... I know below expression is not correct....

------------------------
| id  | Name  | Member
------------------------
| 1   | TeamA | 123, 456

You're correct to make a users table that lists the user's team. Not sure you need a column for members for each team. You can query with a join on team and users then group by team id to get the data. — anna, Dec 06 '19 at 19:18
why do you need to have child table references in parent table? if this 1 to many relationship, then having just TeamId would solve the (most of the) scenarios. So I would like to hear the use case on why you want to have references? — sam, Dec 06 '19 at 19:21
Maybe like this: https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows — xQbert, Dec 06 '19 at 19:33

score 1 · Answer 1 · answered Dec 06 '19 at 19:23

(Table) Team:

TeamId(PK), Name

(Table) Member:

MemberId(Pk), Name

Case 1 (one to many): Every Member can be in one team: Simply add TeamId(FK) to Member Table

Case 2 (many to many): Every Member can be in as many teams as you want: Add a linking table:

(Table) TeamMember:

TeamId(FK), MemberId(FK)

Himanshu · Answer 2 · 2019-12-06T19:29:11.943

0

That indeed means to normalize data further meaning to split tables your data isnt atomic at each column level. Hence should be normalized to avoid difficulties in data retrievel.

Users Table

   User Id(pk) TeamId(fk)

Members Table

 TeamId(pk) , MemberId, NAME

Query

   Select 
   u.userid, memberid, m.namefrom 
      users u join members m
   On u.userid=m.memberid and
    u.teamid=m.teamid

edited Dec 06 '19 at 19:29

answered Dec 06 '19 at 19:23

Himanshu

3,830
2
10
29

score -1 · Accepted Answer · answered Dec 06 '19 at 19:16

-1

It looks like you need to create a mapping table to represent the Team, Member relationship, which would make this a many-to-many relationship.

TeamMemberMap - TeamId, MemberId

answered Dec 06 '19 at 19:16

Christopher Bales

1,069
1
13
23

ty for your time! I thought about having a mapping table but I couldn't refer from `Team` to `Mapping` table in unique way... I think I realized misunderstanding. I don't need to keep `Members` column. – DaeYoung Dec 06 '19 at 19:25
or as you and @Pilan said, I could use this mapping table just having `TeamId(FK), MemberId(FK)`. This would work for me also. – DaeYoung Dec 06 '19 at 19:52

How can I separate out multiple values from a column via another table?

3 Answers3