On Thu, Dec 11, 2008 at 12:56 PM, tedd <tedd.sperling@xxxxxxxxx> wrote: > 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 > > Nope, You can't use Fks for this. Foreign keys are constraints and are used to limit the data in the table. If you were to put an FK on the courses table referencing the instructors table on the instructor id, the instructor id must exist in the instructor table before db will allow the entry of the instructor id in the courses table. The idea is to force data integrity in a system such that certain records can't be created without having the necessary backup data from another table. Joins should allow you to pull the data from the table in one query hth -- Bastien Cat, the other other white meat
