Skip to main content
Notice removed Canonical answer required by Dean7
Bounty Ended with J.D.'s answer chosen by Dean7
formatted question; enhanced grammar; fixed minor typographical errors; introduced lang-none
Source Link
John K. N.
  • 18.3k
  • 14
  • 54
  • 115

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

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.

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
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

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

EDIT 2: This is a junction table:

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

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.

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
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
 

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
Notice added Canonical answer required by Dean7
Bounty Started worth 50 reputation by Dean7
edited code, and gave new code as suggested in comments
Source Link
Dean7
  • 51
  • 5
ID Id SET_NAME Set_Name                        SET_IDSet_Id   MINIFIGURES_LISTMinifigures_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
.....
CREATE TABLE Sets (
ID int NOT NULL AUTO_INCREMENT,
SET_NAMESet_Name varchar(255),
SET_IDSet_Id varchar(255),
);
Insert into Sets (SET_NAMESet_Name, SET_IDSet_Id)
VALUES ('Spidermans Doc Ock Ambush' , '6837'), 
('Spider-Man: Spider-Cycle Chase' , '76004')
('Electro', '5002125')
;
...
MIN_IDMin_Id  MINIFIG_NAMEMinifig_Name    MINIFIG_IDMinifig_Id
1       Doc Ock         SH040
2       Spider-Man      SH038
3       Venom           SH055
4       Nick Fury       SH056
5       Iron Fist       SH041
6       Electro         SH141
CREATE TABLE MINIFIGS (
ID int NOT NULL AUTO_INCREMENT,
MINIFIG_NAMEMinifig_Name varchar(255),
MINIFIG_IDMinifig_Id varchar(255),
);
Insert into Minifigs (MINIFIG_NAMEMinifig_Name, MINIFIG_IDMinifig_Id)
VALUES ('Doc Ock' , 'SH040'), 
('Spider-Man', 'SH038')
('Venom', 'SH055')
;
...
ID  SET_NAMESet_Name                        SET_IDSet_Id  MINIFIG_NAMEMinifig_Name   MINIFIG_IDMinifig_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_NAMESet_Name                         SET_IDSet_Id   MINIFIGURES_LISTMinifigures_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
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
.....
CREATE TABLE Sets (
ID int NOT NULL AUTO_INCREMENT,
SET_NAME varchar(255),
SET_ID varchar(255),
);
Insert into Sets (SET_NAME, SET_ID)
VALUES ('Spidermans Doc Ock Ambush' , '6837'), 
('Spider-Man: Spider-Cycle Chase' , '76004')
('Electro', '5002125')
;
...
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
CREATE TABLE MINIFIGS (
ID int NOT NULL AUTO_INCREMENT,
MINIFIG_NAME varchar(255),
MINIFIG_ID varchar(255),
);
Insert into Minifigs (MINIFIG_NAME, MINIFIG_ID)
VALUES ('Doc Ock' , 'SH040'), 
('Spider-Man', 'SH038')
('Venom', 'SH055')
;
...
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   MINIFIGURES_LIST
 99 Web Warriors Bridge Battle       76057    Aunt May, Spider-Woman, Spider-Man, Kraven, Green Goblin, Scorpion, Scarlet Spider

Thank you.

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
.....
CREATE TABLE Sets (
ID int NOT NULL AUTO_INCREMENT,
Set_Name varchar(255),
Set_Id varchar(255),
);
Insert into Sets (Set_Name, Set_Id)
VALUES ('Spidermans Doc Ock Ambush' , '6837'), 
('Spider-Man: Spider-Cycle Chase' , '76004')
('Electro', '5002125')
;
...
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
CREATE TABLE MINIFIGS (
ID int NOT NULL AUTO_INCREMENT,
Minifig_Name varchar(255),
Minifig_Id varchar(255),
);
Insert into Minifigs (Minifig_Name, Minifig_Id)
VALUES ('Doc Ock' , 'SH040'), 
('Spider-Man', 'SH038')
('Venom', 'SH055')
;
...
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   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
added question
Source Link
Dean7
  • 51
  • 5

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

And then, where and how i do insert new data?

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

Source Link
Dean7
  • 51
  • 5
Loading