Hi gang:
I know this is a MySQL question, but I get a better reply from this
group than the MySQL list (no offense meant to that list) -- In any
event, here goes.
I currently have a project that's a classic example of a relational
database, namely the course, student, and instructor problem.
I have my database set-up such that there are tables for courses,
students, and instructors -- each table with it's own unique/primary
ID.
The Course table has a field for the Instructor, which is the primary
ID found in the Instructor's table.
Likewise, the Course table also has fields for up-to four students (a
private tutorial service). These will be filled by the the respective
primary ID's found in the Student's table.
This configuration is "Second Normal Form".
The Course table also has fields for date, times and such.
If I want to see the schedule for any specific day, I simply open the
Course table and search for date and time. From that I can retrieve
the Student ID's and the Instructor ID.
From those, I open the Student table and pull out all students who
attend the class and then open the Instructor's table and pull out
what data I need re the Instructor.
Everything works.
My question is, can using Foreign Keys make this work better? Can I
retrieve all this data in one query? (I think that's what foreign
keys do -- but, I'm assuming that).
I believe I can create the Course table (excluding everything not
germane) like so:
INDEX (student_id),
FOREIGN KEY (student_id)
REFERENCES (students(id)),
INDEX (instructor_id),
FOREIGN KEY (instructor_id)
REFERENCES (instructors(id)),
But how do I use Foreign Keys to retrieve data? What is the specific
query to pull out data from all three tables at once?
Does anyone have any example code to show me the actual syntax?
Thanks and Cheers,
tedd
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
