I have a large database of coaches with the following tables. Coaches table - id, fname, lname, collegeid College table - id, college, address, mascotid, stateid. State table - id, state, abbreviation Mascot table - id, mascot I'm stil trying to wrap my head around DB normailization, but it seems that there shouldn't be any repeating values so I add FK's (mascotid, stateid, collegeid) to the respective tables. My only question now is do i really have to do 3+ queries to retrieve all the values for a coach? First query for the coach information, then query for the coaches college by the collegeid, then query the college for its state (stateid) and mascot (mascotid)? Is there an easier way of doing this? As a side note I'm using PHP.
The good news is you have obviously read up on normalization and tried to apply it. The bad news is that I think you have over normalized your data. First, I don't think you need a Mascot table. A general rule to use is that a table with only one real field (non-id) doesn't need to be its own table. Simply put the Mascot value in the College table. Second, for states I always make the postal code the primary key because they are unique in and of themselves. For your database I would eliminate the id field in the State table and put the abbreviation of that state in the College table instead of the stateid field. Now to pull the data out with multiple tables, you use a simple join which essentially links all the tables together using a WHERE clause. Here is what the query to get all the actual data for a coach: SELECT fname, lname, college, address, mascot, state FROM Coaches, College, State WHERE Coaches.collegeid=College.id AND College.stateabbreviation=State.abbreviation; PHP: Note: I used my updated table structure, which includes replacing the stateid field in the College table with stateabbreviation field.
Yeah - in theoretical database design normalisation is really important, but in the real world you often want to normalise only when necessary. A fully normalised database can often be slower in highly transactional web environments than a slightly less normalised one. I would lay out my tables a bit like this: * Coaches table - id, fname, lname, etc. * College table - id, college, address, coachid, mascotname, statecode. * State table - statecode, statename ... assuming a coach could be at multiple colleges, and the college can only have one mascot. And then once you've done that, to get a list of all colleges and coaches just do something like: SELECT college, address, mascotname, statecode, statename, fname, lname FROM college INNER JOIN coaches ON coaches.id = college.coachid INNER JOIN states ON states.statecode = colleges.statecode