The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

multiple many to manys


I have serval many to many relationships in a MySQL database and im trying to pull data from these tables.

Does anyone know of any good tutorials that cover pulling data from more than 2 tables?

Many thanks
a young one
Wednesday, May 10, 2006
In the sense of understanding left joins, right joins, inner joins and outer joins?

Or in the sense of organizing the tables and the data to require as little work as possible?
Wednesday, May 10, 2006
Use Ruby On Rails. It's made good database design redundant.
Database purist
Thursday, May 11, 2006
==>Use Ruby On Rails. It's made good database design redundant.

Disregard that statement. "good database design" will *never* be redundant, and will always be the key in good systems design. If your datbase is FUBARed, then *everything* will be FUBARed. The above statement is just about the worst piece of advice I've ever heard on this forum (nothing personal to the guy who made the statement).

Without a solid database foundation, any application developed against the database will be for shite. Understand the fundamentals first -- then have a look at the suggested "Ruby On Rails" and see if it fits.
Thursday, May 11, 2006
Many to many = bad news.

You need to take the many to many out.

You want 99% of all relationships to be 1 to many (in most cases).

So let's use an example.

Let's say Jack & Jill own two dogs, Frick and Frack.

So you have a many to many between the 'Pet Owners' table and the 'Pets' table.

You'll want to use/create what is called a junction table. Sometimes this is incorrectly referred to as a lookup table.

Anyways, you need a junction table called "Pet Owners Pets" or something similar.

Each record in the junction table will define the relationship between one pet owner and one pet:

Jack | Frick
Jack | Frack
Jill | Frick
Jill | Frack

You should probably use a unique id of sorts instead of names of course.

Let me know if you have any questions.
D in PHX Send private email
Thursday, May 11, 2006
"Many to many = bad news."

+1 to that; I was always taught (back in the dark ages, I admit) that many to many is an absolute no-no in an RDBS. Object databases can do many to many (basically by encapsulating the "link table" or whatever you wish to call it) but if you do many to many (presumably by having foreign keys in both tables) then the DB engine won't be able to handle your referential integrity for you amongst other problems.
Paul Brown Send private email
Friday, May 12, 2006
Cheers for the feedback guys.

Yeah I didn't mean physical many to manys I meant when you have a many to many then break it down to 2 one to manys.

I had a few of these and needed some clarity with JOINS but I think i've sorted it now :)
a young one
Saturday, May 13, 2006
Glad to hear you got it.

Shoot me an email if you need a hand.
D in PHX Send private email
Tuesday, May 16, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz