Many to Many - Concept

Question

I know there are a lot questions about MANY TO MANY relationship, but none seem to work for me, because it is more of a question about the concept rather than one of code. So I need some clarification on how to implement my idea.

I am trying to make a LEGO database. I have SETS which can have many MINIFIGURES. Also certain MINIFIGURES can appear in many SETS. I was doing it like this, in Excel(csv) file. And it was SETS "table".

Id  Set_Name                        Set_Id   Minifigures_List
1   Spider-Man's Doc Ock Ambush     6873     Doc Ock, Spider-Man, Iron Fist
2   Spider-Man: Spider-Cycle Chase  76004    Spider-Man, Venom, Nick Fury
3   Electro                         5002125  Electro
.....

First step of my database implementation was to create a SETS table.

CREATE TABLE Sets (
ID int NOT NULL AUTO_INCREMENT,
Set_Name varchar(255),
Set_Id varchar(255),
);

And then insert data:

Insert into Sets (Set_Name, Set_Id)
VALUES ('Spidermans Doc Ock Ambush' , '6837'), 
('Spider-Man: Spider-Cycle Chase' , '76004')
('Electro', '5002125')
;
...

Next step was to create a MINIFIGS table, and give them attributes.

Min_Id  Minifig_Name    Minifig_Id
1       Doc Ock         SH040
2       Spider-Man      SH038
3       Venom           SH055
4       Nick Fury       SH056
5       Iron Fist       SH041
6       Electro         SH141

Like this:

CREATE TABLE MINIFIGS (
ID int NOT NULL AUTO_INCREMENT,
Minifig_Name varchar(255),
Minifig_Id varchar(255),
);

And then insert data:

Insert into Minifigs (Minifig_Name, Minifig_Id)
VALUES ('Doc Ock' , 'SH040'), 
('Spider-Man', 'SH038'),
('Venom', 'SH055')
;
...

How to proceed from here? Am I missing a step? Where and how I should do it? Where is a step to relate tables? Do I have to do it like this, first split Minifigures_Sets cell, and create table from this? If I create table like this, there will be duplicates. Where is a step to create junction table?

ID  Set_Name                        Set_Id  Minifig_Name   Minifig_Id
1   Spider-Man's Doc Ock Ambush     6873    Doc Ock        SH040
2   Spider-Man's Doc Ock Ambush     6873    Spider-Man     SH038
3   Spider-Man's Doc Ock Ambush     6873    Iron Fist      SH041
4   Spider-Man: Spider-Cycle Chase  76004   Spider-Man     SH038
5   Spider-Man: Spider-Cycle Chase  76004   Venom          SH055
6   Spider-Man: Spider-Cycle Chase  76004   Nick Fury      SH056
7   Electro                         5002125 Electro        SH141

And then, where and how do I insert new data? Into Junction table? Can main tables pull data from junction table?

ID  Set_Name                         Set_Id   Minifigures_List
99  Web Warriors Bridge Battle       76057    Aunt May, Spider-Woman, Spider-Man, Kraven, Green Goblin, Scorpion, Scarlet Spider

Thank you.

---

This is a junction table I created after reading the possible answer and comments:

CREATE TABLE Minifigures_Sets ( 
  Minifig_Id INT NOT NULL,
  Minifig_Name varchar(255),
  Set_Id INT NOT NULL,
  Set_Name varchar(255)
);

And now, where and how do I insert this new data, when I get it? Do I put it just in a junction table? Or I first have to split Minifigure_List cell, and insert into Minifigs and Sets table first? Do I need some commands like REFERENCES, and maybe insert and define foreign keys, but I am not sure where to do it?

ID  Set_Name                         Set_Id   Minifigures_List
99  Web Warriors Bridge Battle       76057    Aunt May, Spider-Woman, Spider-Man, Kraven, Green Goblin, Scorpion, Scarlet Spider
77  Attack on the Spider Lair        76175    Spider-Man, Venom, Green Goblin

Answer 1

Anything that can exist on its own, without reference to anything else, needs to have its own table, so that gives you two to start with:

select * from Sets ; 

+----+--------------------------------+---------+
| id | name                           | lego_id | 
+----+--------------------------------+---------+
|  1 | Spider-Man's Doc Ock Ambush    | 6873    |
|  2 | Spider-Man: Spider-Cycle Chase | 76004   |
|  3 | Electro                        | 5002125 |
+----+--------------------------------+---------+

select * from Minifigs ; 

+----+------------+---------+
| id | name       | lego_id | 
+----+------------+---------+
| 11 | Doc Ock    | SH040   |
| 22 | Spider-Man | SH038   |
| 33 | Venom      | SH055   |
| 44 | Nick Fury  | SH056   |
| 55 | Iron Fist  | SH041   |
| 66 | Electro    | SH141   |
+----+------------+---------+

Where you need to relate things together, you use a linking table that carries just the information you need to link to two and, since you're using unique ids, that's all you need:

select * from Set_Minifigs ; 

+--------+-------+
| set_id | mf_id | 
+--------+-------+
|      1 |    11 | 
|      1 |    22 | 
|      1 |    55 | 
|      2 |    22 |
|      2 |    33 |
|      2 |    44 |
|      3 |    66 |
+--------+-------+

Doesn't look like much, but it comes into its own when you start joining them back together.

select s.id
, s.name
, s.lego_id 
, GROUP_CONCAT( m.name ) minifigs 
from Sets s
inner join Set_Minifigs sm 
      on s.id = sm.set_id
inner join Minifigs m 
      on sm.mf_id = m.id 
order by 1 ; 

+----+--------------------------------+----------+------------------------------+
| id | name                           | lego_id  | minifigs                     |
+----+--------------------------------+----------+------------------------------+
|  1 | Spider-Man's Doc Ock Ambush    | 6873     | Doc Ock,Spider-Man,Iron Fist |
|  2 | Spider-Man: Spider-Cycle Chase | 76004    | Spider-Man,Venom,Nick Fury   |
|  3 | Electro                        | 5002125  | Electro                      |
+----+--------------------------------+----------+------------------------------+

Look familiar?

Answer 2

You'll need a third linking table (junction table) as you mentioned but it should solely consist of the keys between the two tables being linked to reduce redundancy and improve normalization.

So you should create a table with a name like MINIFIGURESETS that contains the columns ID from your SETS table (should give it a better name but you already had another column called SET_ID, so that could get confusing) and MIN_ID from your MINIFIGS. It also could have its own identity column if you want.

That table will always manage the many-to-many relationship between your SETS and MINIFIGS tables. When a new set is added, new minifigures are created, or a minifigure is removed from a set, the MINIFIGURESETS table is where you'll manage those actions.

As mentioned in my comments, you'll need to insert into your MINIFIGS and/or SETS tables first to generate the keys of each record you need, once those are created you can use the keys from each table to insert into your linking/junction table. Without knowing which exact database system, implementation, and version you're using, it's not really possible to provide exact code you can leverage (if you update your post with this information then I'll update my answer too). But to give you an idea, in Microsoft SQL Server for example, there's something a function you can call after INSERT called SCOPE_IDENTITY(). Here's an example query batch that leverages SCOPE_IDENTITY() so you can get the key of a newly inserted record into the MINIFIGS table and then insert that key into your linking/junction table for a specific SET like so:

-- Creates a new Minifigure
INSERT INTO Minifigs (Minifig_Name, Minifig_Id)
VALUES ('Doc Ock' , 'SH040');

-- Created a new link between the new Minifigure and the Set with SET_ID 76057 (for the Set called Web Warriors Bridge Battle)
INSERT INTO Minifigures_Sets (Minifig_Id, Set_Id)
SELECT SCOPE_IDENTITY() AS Minifig_Id, 76057 AS Set_Id

You can even use the same function to map a newly created MINIFIG to all the SETS like so:

-- Creates a new Minifigure
INSERT INTO Minifigs (Minifig_Name, Minifig_Id)
VALUES ('Spider-Man' , 'SH038');

-- Created a new link between the new Minifigure and ALL Sets
INSERT INTO Minifigures_Sets (Minifig_Id, Set_Id)
SELECT SCOPE_IDENTITY() AS Minifig_Id, Set_Id
FROM Sets

Again please note that your mileage will vary on how to get the key of a newly inserted record into a table based on the specific database system your using, and the above example is just to give a general idea (based on what's available in Microsoft SQL Server).

I noticed you updated your post with your linking/junction table but you've included the Minifig_Name and Set_Name as columns in it. These two columns are unneeded and lead to unnecessary data redundancy. You should remove them from the Minifigures_Sets table and only store the ID columns you've added. Whenever you need one of the ...Name columns you can JOIN to the correlating table by ID to get them like so:

SELECT MS.Minifig_Id, MS.Set_Id, M.Minifig_Name, S.Set_Name
FROM Minifigures_Sets MS
INNER JOIN Minifigs M
    ON MS.Minifig_Id = M.Minifig_Id
INNER JOIN Sets S
    ON MS.Set_Id = S.Set_Id

The final thing I'll say is (and my earlier recommendation meant) that your linking/junction table should store the keys of your Minifigs and Sets tables which would be the ID columns of each. You would just need to name them appropriately because you already are using the names Minifig_Id and Set_Id in their respective tables to mean a different field. (I'd personally rename those to something else, so you can use those names to represent the keys which would be more clear when you use them in your Minifigure_Sets table.)