Are you a Google Analytics enthusiast?
More SEO Content
Posted 21 February 2010 - 02:24 PM
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?
Posted 21 February 2010 - 02:29 PM
Posted 23 February 2010 - 12:14 PM
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.
Posted 05 March 2010 - 02:04 PM
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users