Jump to content

  • Log in with Facebook Log in with Twitter Log In with Google      Sign In   
  • Create Account

Subscribe to HRA Now!

 



Are you a Google Analytics enthusiast?

Share and download Custom Google Analytics Reports, dashboards and advanced segments--for FREE! 

 



 

 www.CustomReportSharing.com 

From the folks who brought you High Rankings!



Photo
- - - - -

Database Design


  • Please log in to reply
3 replies to this topic

#1 ruffy

ruffy

    HR 1

  • Members
  • Pip
  • 2 posts

Posted 21 February 2010 - 02:24 PM

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?

#2 Randy

Randy

    Convert Me!

  • Moderator
  • 17,540 posts

Posted 21 February 2010 - 02:29 PM

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

#3 Ron Carnell

Ron Carnell

    HR 6

  • Moderator
  • 963 posts
  • Location:Michigan USA

Posted 23 February 2010 - 12:14 PM

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. smile.gif




#4 Schevikhoven

Schevikhoven

    HR 2

  • Members
  • PipPip
  • 17 posts
  • Location:Finland

Posted 05 March 2010 - 02:04 PM

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






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

SPAM FREE FORUM!
 
If you are just registering to spam,
don't bother. You will be wasting your
time as your spam will never see the
light of day!