3 replies to this topic

[ruffy]

How do I handle this Mysql 4-table scenario:

SCHOOLS to STUDENTS is a 1 to many relationship.
AUCTIONS to PRIZES is also 1 to many.

Now, a school can participate in many auctions,
or one auction can be registered with many schools.
That is, SCHOOLS to AUCTIONS is a many to many realtionship.

Since STUDENTS to PRIZES can be 1 to many,
how do I handle this table setup - to query winners of prizes or auctions?

[Randy]

Make sure to give each some type of unique ID. Then you can cross reference between the tables based upon this unique ID.

[Ron Carnell]

Dodd's relational database model rests on the foundation of a one-to-many relationship. One-to-one relationships can almost always be safely ignored, and many-to-many relationship can almost always be converted to one or more one-to-many relationships. Doing so is the essence of "normalizing" your db design.

What Randy is describing is typically called the Primary Key. The PK uniquely identifies a record and, usually, an entity. For example, every school should have a PK. Every auction should also have a PK. To normalize this many-to-many relationship, you use what is often called a transaction table.

TransactionID Primary Key for the transaction table
SchoolID The PK for the school (called a Foreign Key when used here)
AuctionID A FK pointing to the PK for the auction
(... any additional fields, like dates or notes)

It then becomes simple to select all the records from the Transaction table for school XYZ, or alternatively, select all auctions ABC and discover which school is involved with that auction. If you already have a one-to-many relationship between schools and students, an inner join will probably get you down to the student level. Or, if students can engage in multiple auctions or win multiple prizes (back to many-to-many), you might need additional transaction tables.

Essentially, any time you run into a many-to-many relationship, you'll probably want to design a transaction table to create a one-to-many relationship. To paraphrase Einstein, you need to keep it as simple as you possibly can . . . but no simpler.

[Schevikhoven]

Ron already covered most of the important stuff about the db design. I was pondering with the very same n:m relationships a few years ago. To be able to query on those just work table by table from the smaller set of records to the result. As a rule of tumb, just print out/draw/imagine your tables on a piece of paper and then draw a like through every column involved in the query and you have the basic structure of the query done =)