MySql - Avoid SET() using junction and foreign keys

Question

So here is the problem :

I have a table "Members" with members and their attributes (name, birthday, mail, etc.) These members may belong to groups (let's say there are 3 groups), from none to all of them. And these groups are referenced in a table ("Groups") so I can add/delete/modify them as I want.

SET() doesn't seem to be a solution, it isn't compatible with foreign keys / reference table.

So at first, I was thinking of doing a TINYINT() column, which I use like SET() : 111 (7) for all groups, 000 (0) for none, 001 (1) for the 1st group , 010 (2) for the 2nd, etc. But since the names are quite complex, it's confusing, and not much more compatible with foreign keys.

I read that I should do a 3rd table "Members-Groups" with memberID and groupID to join both of my two tables, but I don't clearly understand how it work.

What I understand is that I will have a table with IDs of members and groups like this :

+----------+---------+
| memberID | groupID |
+----------+---------+
| 1        | 1       |
| 1        | 2       |
| 2        | 1       |
| 2        | 3       |
| 3        | 2       |
+----------+---------+

and combined with junction I can retrieve what I want. Is it right ? Otherwise can someone explain me how i should do ?

I precise that I'd like to have as final result (after sql request + php script) a member, his attributes and the groups he belongs to in a single row (as with SET()), even members that doesn't belong to any group.

Answer 1

I feel half-confused by the question, but I'll take a stab at it.

If you have a Members table, then it makes sense to have member_id be the unique primary key. If you want to store which groups each member is in, simply add a new column to the Members table for each Group.

As for the values to be given to columns Group1, Group2, Group3 you could set them as ENUM('0','1') or ENUM('No','Yes') or whatever and make the default value the negative-meaning (first) value.

With this db structure, you won't have to bother chopping up a string during querying -- you just write SELECT or WHERE statements that specify the appropriate Group column value.

If this doesn't directly answer, please clarify your question.

Answer 2

Assuming

drop table if exists mg;

drop table if exists m;
create table m (id int primary key, name varchar(3));
insert into m values
(1,'abc'),
(2,'def'),
(3,'ghi');

drop table if exists g;
create table g(id int primary key ,name varchar(3));
insert into g values
(1,'aaa'),
(2,'bbb'),
(3,'ccc');

create table mg
(memid int,grid int,
index fmid(memid,grid) ,
foreign key (memid) references m(id) on delete cascade,
foreign key (grid)  references g(id) on delete cascade
);
insert into mg values
(1,1),(1,2),(1,3),
(2,1),(2,3);

You could join the 3 tables and produce the results using group_concat or conditional aggregation.

MariaDB [sandbox]> select m.id,m.name, group_concat(g.name) groups
    -> from m
    -> join mg on mg.memid = m.id
    -> join g on mg.grid = g.id
    -> group by m.id,m.name;
+----+------+-------------+
| id | name | groups      |
+----+------+-------------+
|  1 | abc  | aaa,bbb,ccc |
|  2 | def  | aaa,ccc     |
+----+------+-------------+
2 rows in set (0.00 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> select m.id,m.name,
    -> max(case when g.id = 1 then g.name else '' end) as group1,
    -> max(case when g.id = 2 then g.name else '' end) as group2,
    -> max(case when g.id = 3 then g.name else '' end) as group3
    -> from m
    -> join mg on mg.memid = m.id
    -> join g on mg.grid = g.id
    -> group by m.id,m.name;
+----+------+--------+--------+--------+
| id | name | group1 | group2 | group3 |
+----+------+--------+--------+--------+
|  1 | abc  | aaa    | bbb    | ccc    |
|  2 | def  | aaa    |        | ccc    |
+----+------+--------+--------+--------+
2 rows in set (0.00 sec)

If you want members who don't belong to any group change the joins to left joins.

ariaDB [sandbox]> select m.id,m.name, group_concat(g.name) groups
    -> from m
    -> left join mg on mg.memid = m.id
    -> left join g on mg.grid = g.id
    -> group by m.id,m.name;
+----+------+-------------+
| id | name | groups      |
+----+------+-------------+
|  1 | abc  | aaa,bbb,ccc |
|  2 | def  | aaa,ccc     |
|  3 | ghi  | NULL        |
+----+------+-------------+
3 rows in set (0.00 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> select m.id,m.name,
    -> max(case when g.id = 1 then g.name else '' end) as group1,
    -> max(case when g.id = 2 then g.name else '' end) as group2,
    -> max(case when g.id = 3 then g.name else '' end) as group3
    -> from m
    -> left join mg on mg.memid = m.id
    -> left join g on mg.grid = g.id
    -> group by m.id,m.name;
+----+------+--------+--------+--------+
| id | name | group1 | group2 | group3 |
+----+------+--------+--------+--------+
|  1 | abc  | aaa    | bbb    | ccc    |
|  2 | def  | aaa    |        | ccc    |
|  3 | ghi  |        |        |        |
+----+------+--------+--------+--------+
3 rows in set (0.00 sec)