Skip to main content
Database Administrators

Return to Revisions

3 of 4
edited code, and gave new code as suggested in comments
Dean7
Dean7
  • 51
  • 5

Many to Many - Concept

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

I am trying to make LEGO database. I have SETS which has many MINIFIGURES. Also MINIFIGURES 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 databse implementation was to 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 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, 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

And then, where and how i do 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.

Edit 1: Removed CAPS LOCK (Based on a first comment)

EDIT 2: This is a junction table:

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 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
Dean7
  • 51
  • 5