I know there are a lot questions about MANY TO MANY relationship, but none seemesseem to work for me, because it is more of a question about the concept rather than aone 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 hascan 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
.....
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 databsemy database implementation was to create a SETS table.
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
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
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, or? 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
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 i 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
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
Edit 1: Removed CAPS LOCK (Based on a first comment)
EDIT 2: This is a junction table I created after reading the possible answer and comments:
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
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