We help IT Professionals succeed at work.

How can I create a many to many relationship in mysql?

FairyBusiness
on
769 Views
Last Modified: 2012-05-11
Hi, my professor this last semester showed us how to do a many to many relationship in MySQL, but he only used 1 day to go over it and I did not fully understand it at the time. So now I need to have a many to many relationship, but I am not sure how to create this. (trying to avoid listed fields) Not even sure where to start.  (I have only ever done one to many relationships)

If someone could please describe a SIMPLE way of doing this, or knows of a SIMPLE tutorial that would be great!
Comment
Watch Question

Data Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:


A many-to-many relationship does not require a third table.


Kent

Author

Commented:
I dont get how you would do a many to many relationship without a third table??  which table are the rows that relate to multiple rows on both sides of the two tables???
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Taking this a step further, you could actually put all of the data in the books table.  The many-to-many relationship occurs because you can have the same author occur several times and you can have the same title occur several times.


Kent

Author

Commented:
but if you have more than one item on the other side needing the title how do you specify where the title goes to which at what time?  i'm just not getting it. .. I know how to create tables and give things the same name but that doesnt help me create a many to many relationship bc i'm not looking at it any new way than before
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Ok.  Back to the books table.

INSERT INTO books (isbn, author, title) VALUES ('00001', "Alan", "English Dictionary");
INSERT INTO books (isbn, author, title) VALUES ('00002', "Barb", "Latin for Beginners");
INSERT INTO books (isbn, author, title) VALUES ('00003', "Chad", "English Dictionary");
INSERT INTO books (isbn, author, title) VALUES ('00004', "Dave", "What to Wear");
INSERT INTO books (isbn, author, title) VALUES ('00005', "Earl", "Beginning Math");
INSERT INTO books (isbn, author, title) VALUES ('00006', "Dave", "Peyton Place");

A many-to-many relationship exists between the author and title columns.  Dave has written two books ("What to wear" and "Peyton Place").  A book titled "English Dictionary" was written by both Alan and Chad.

If the Authors and Title are moved into separate tables the many-to-many relationship exists across tables.


Kent

Author

Commented:
It still looks like a one to many relationship in my mind. . .  

you have one book english dictionary, two authors

or you have one author dave, and two books


maybe I am missing something. . .
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
ok only the 3 tables makes any sense to me. .. so can someone give me an example of how that would look?

Author

Commented:
nope that didnt work.

here is my entire function if it helps:

function woods() {
	global $conn;
	if(isset($_GET['id'])) {
		$id = $_GET['id'];
		$query = "SELECT name FROM submenus WHERE id=" . $id;
		$result = mysql_query($query, $conn);
		confirm_query($result);
		while($rows = mysql_fetch_assoc($result)) {
			$name = $rows['name'];
		}
		$path = "flipbook/" . $name;
		$dirs = scandir($path);
		foreach($dirs as $dir) {
			echo $dir . "<br />";
			$data = $path . "/" . $dir;
			echo $data . "<br />";
			$style = scandir($data);
			//print_r($style[4]);
			if(!is_dir($style)) {
				foreach($style as $img) {
					if (!is_dir($img)){
						echo "<img src=\"" . $data . "/" . $img . "\" />";
					}
				}
			}			
		echo "<br />";
		}
	}
}

Open in new window

Author

Commented:
ignore that last comment, was for another question.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Fairy,

Don't think of the database structure, think of the data.  The relationship is between data objects.  The container is immaterial.


Kent

Author

Commented:
ok, I have 3 necklaces

they each contain many gemstones:

rubies
diamonds
sapphires
quartz

so all 3 necklaces contain rubies, so rubies would to three different ones with 3 coming at it

how would that work specifically? so me a row that demonstrates the many to many relationship
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:

Easy enough.

Necklace 1   Rubies
Necklace 1   Diamonds
Necklace 1   Sapphires
Necklace 1   Quartz
Necklace 2   Rubies
Necklace 3   Rubies
Necklace 3   Diamonds

Open in new window


Author

Commented:
what if I have like 50 gemstones per necklaces? and like 50 necklaces?
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
The volume doesn't matter, as long as each side of the relationship supports a quantity greater than 1.

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
thanks everyone
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.