Re: [PHP] Foreign Keys Question
Waynn Lue wrote: As a side note, FKs do enforce other table specific properties like indexes on the fields being constrained, so they do add value there as well. But there's of course an extra cost on updates and inserts to see if the FK is violated. On the external table? No they don't. mysql create table t1(id int primary key, name varchar(255)) engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql create table t2(t2id int, t1id int references t1(id)) engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql show create table t2\G *** 1. row *** Table: t2 Create Table: CREATE TABLE `t2` ( `t2id` int(11) default NULL, `t1id` int(11) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) No auto-index on t2(t1id) at all. You have to define that yourself - you might want it part of a multi-column index for example. You definitely should index it, but it won't happen automatically. Hm, that's weird. Which version of mysql are you using? According to http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html: InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the *first* columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) *index_name*, if given, is used as described previously.
Re: [PHP] Foreign Keys Question
At 4:25 PM -0500 12/11/08, Robert Cummings wrote: On Thu, 2008-12-11 at 16:24 -0500, Robert Cummings wrote: lock table check enrolment count no room unlock table generate error have room insert row unlock table Ba da boom. I should have read your message better... you were talking about skipping transactions and not locking :) Cheers, Rob. Rob: I was talking about both -- you answer was fine. Thanks, 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
[PHP] Foreign Keys Question
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
Re: [PHP] Foreign Keys Question
On Thu, Dec 11, 2008 at 12:56 PM, tedd tedd.sperl...@gmail.com 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
Re: [PHP] Foreign Keys Question
Hey tedd --- Do I understand your structure correctly that you have something like: Courses (table) course_id, subject_id, student1_id, student2_id, student3_id, student4_id, etc. Is that right? Ken -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Foreign Keys Question
At 12:29 PM -0600 12/11/08, phphelp -- kbk wrote: Hey tedd --- Do I understand your structure correctly that you have something like: Courses (table) course_id, subject_id, student1_id, student2_id, student3_id, student4_id, etc. Is that right? Ken Ken: That was right, but I think I've reconsidered. You see, Richard pointed out the error of my ways, namely: Student table has student data and ID -- as may records as there are students. Instructor table has instructor data and ID -- as may records as there are instructors. Course table has course data, ID and Instructor ID and Student ID -- but the number of records vary. While there will be only one record per Instructor and one pre Student, the Course table can have up to four records for each course depending upon actuall attendance. I think that makes more sense. As for my Foreign Keys Question, I think the answer is that it enforces rules upon the configuration (i.e., deleting, altering, and such), but does not provide any significant service beyond that. 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
Re: [PHP] Foreign Keys Question
On Thu, 2008-12-11 at 13:27 -0500, Bastien Koert wrote: On Thu, Dec 11, 2008 at 12:56 PM, tedd tedd.sperl...@gmail.com 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? 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 You want to be careful using joins though. If you were to pull all that data in one shot using joins you would create a large data transfer mostly containing redundant data. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Foreign Keys Question
At 1:48 PM -0500 12/11/08, Robert Cummings wrote: On Thu, 2008-12-11 at 13:27 -0500, Bastien Koert wrote: On Thu, Dec 11, 2008 at 12:56 PM, tedd tedd.sperl...@gmail.com Does anyone have any example code to show me the actual syntax? Joins should allow you to pull the data from the table in one query You want to be careful using joins though. If you were to pull all that data in one shot using joins you would create a large data transfer mostly containing redundant data. Cheers, Rob. Rob: Good point. Maybe my old way of doing that was the best. Simply get the ID's needed from one table and then open the necessary tables accordingly. In other words, stop worrying about the number of times I'm going to the store to buy things because going once might result in the things being too heavy for me to carry home -- if that makes sense. 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
Re: [PHP] Foreign Keys Question
On Thu, 2008-12-11 at 13:46 -0500, tedd wrote: At 12:29 PM -0600 12/11/08, phphelp -- kbk wrote: Hey tedd --- Do I understand your structure correctly that you have something like: Courses (table) course_id, subject_id, student1_id, student2_id, student3_id, student4_id, etc. Is that right? Ken Ken: That was right, but I think I've reconsidered. You see, Richard pointed out the error of my ways, namely: Student table has student data and ID -- as may records as there are students. Instructor table has instructor data and ID -- as may records as there are instructors. Course table has course data, ID and Instructor ID and Student ID -- but the number of records vary. While there will be only one record per Instructor and one pre Student, the Course table can have up to four records for each course depending upon actuall attendance. I think that makes more sense. Student attendance should be a table with rows for each student in attendance. If you want to limit the number make a check before adding another student. Don't hard code the number via fields like above or you'll find it overly ugly to add/remove students or even increase the number of allowed students. As it stands to allow 6 students you would have to add 2 more columns. With attendance controled by a table with a row for ewach attendance you would only need to update a configuration variable the determines the maximim number of allowed students in attendance. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Foreign Keys Question
On Dec 11, 2008, at 12:55 PM, Robert Cummings wrote: On Thu, 2008-12-11 at 13:46 -0500, tedd wrote: At 12:29 PM -0600 12/11/08, phphelp -- kbk wrote: Hey tedd --- Do I understand your structure correctly that you have something like: Courses (table) course_id, subject_id, student1_id, student2_id, That was right, but I think I've reconsidered. You see, Richard pointed out the error of my ways, namely: Student attendance should be a table with rows for each student in attendance. If you want to limit the number make a check before adding another student. Don't hard code the number via fields like above or you'll find it overly ugly to add/remove students or even increase the Right, Rob --- As it happens I'm writing an application like this now, in late beta, ready to go live any day now. I call the table you are describing 'enrollment.' So: STUDENTS - student_id, - (rest of the fields) ENROLLMENT - enroll_id - student_id - course_id - role_in_class - (more fields) COURSE - course_id - subject_id - (more) Except I take it one more step: Instructors are just People, as are Students. The role_in_class field distinguishes what they are doing in the classroom. (We have more roles than those in our application.) Ken -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Foreign Keys Question
On Thu, 2008-12-11 at 13:56 -0500, tedd wrote: At 1:48 PM -0500 12/11/08, Robert Cummings wrote: On Thu, 2008-12-11 at 13:27 -0500, Bastien Koert wrote: On Thu, Dec 11, 2008 at 12:56 PM, tedd tedd.sperl...@gmail.com Does anyone have any example code to show me the actual syntax? Joins should allow you to pull the data from the table in one query You want to be careful using joins though. If you were to pull all that data in one shot using joins you would create a large data transfer mostly containing redundant data. Cheers, Rob. Rob: Good point. Maybe my old way of doing that was the best. Simply get the ID's needed from one table and then open the necessary tables accordingly. In other words, stop worrying about the number of times I'm going to the store to buy things because going once might result in the things being too heavy for me to carry home -- if that makes No, it's still good to think about the number of trips. For instance if you are getting the number of students in attendance you may as well join on the student table to get their individual data if you need it. Similarly, if for whatever reason you have 10 student IDs, but not the student information itself, but you need to get said information... you can just select them all in one query using an IN( id1, id2, ... ) clause. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Foreign Keys Question
At 1:55 PM -0500 12/11/08, Robert Cummings wrote: Student attendance should be a table with rows for each student in attendance. If you want to limit the number make a check before adding another student. Don't hard code the number via fields like above or you'll find it overly ugly to add/remove students or even increase the number of allowed students. As it stands to allow 6 students you would have to add 2 more columns. With attendance controled by a table with a row for ewach attendance you would only need to update a configuration variable the determines the maximim number of allowed students in attendance. Cheers, Rob. Rob: I see -- the solution is a table for courses, students, instructors and enrollment. The enrollment table will hold links to all (third normal). If I want to limit the number of students in a course, then just count the number of students enrolled in a specific course within the enrollment table and check that number against the maximum allowed before adding another enrollment record. Also, if a student cancels, then it's only a deletion of an enrollment record and all is right with the world. That's good. Sometimes it's good to talk things like this through -- clears the fuzzy thinking. Thanks, 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
Re: [PHP] Foreign Keys Question
On Thu, Dec 11, 2008 at 2:32 PM, tedd tedd.sperl...@gmail.com wrote: At 1:55 PM -0500 12/11/08, Robert Cummings wrote: Student attendance should be a table with rows for each student in attendance. If you want to limit the number make a check before adding another student. Don't hard code the number via fields like above or you'll find it overly ugly to add/remove students or even increase the number of allowed students. As it stands to allow 6 students you would have to add 2 more columns. With attendance controled by a table with a row for ewach attendance you would only need to update a configuration variable the determines the maximim number of allowed students in attendance. Cheers, Rob. Rob: I see -- the solution is a table for courses, students, instructors and enrollment. The enrollment table will hold links to all (third normal). If I want to limit the number of students in a course, then just count the number of students enrolled in a specific course within the enrollment table and check that number against the maximum allowed before adding another enrollment record. Also, if a student cancels, then it's only a deletion of an enrollment record and all is right with the world. That's good. Sometimes it's good to talk things like this through -- clears the fuzzy thinking. Thanks, tedd That's the way to go. Just keep in mind that you will have to consider ways of preventing overloading your courses due to concurrency, whether you use table locks or devise your own mechanism. I don't know if you are allowing students to self-enroll or not. You just don't want two users to both see that a course has one opening left and both try to take that spot at the same time. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Foreign Keys Question
At 3:14 PM -0500 12/11/08, Andrew Ballard wrote: That's the way to go. Just keep in mind that you will have to consider ways of preventing overloading your courses due to concurrency, whether you use table locks or devise your own mechanism. I don't know if you are allowing students to self-enroll or not. You just don't want two users to both see that a course has one opening left and both try to take that spot at the same time. Andrew It's very low volume at the moment, so I don't think there will be an immediate problem. However, I will investigate locking the tables for entry. But I don't think using transactions will be necessary. Thanks again for all the help Andrew el al. 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
Re: [PHP] Foreign Keys Question
On Thu, 2008-12-11 at 16:17 -0500, tedd wrote: At 3:14 PM -0500 12/11/08, Andrew Ballard wrote: That's the way to go. Just keep in mind that you will have to consider ways of preventing overloading your courses due to concurrency, whether you use table locks or devise your own mechanism. I don't know if you are allowing students to self-enroll or not. You just don't want two users to both see that a course has one opening left and both try to take that spot at the same time. Andrew It's very low volume at the moment, so I don't think there will be an immediate problem. However, I will investigate locking the tables for entry. But I don't think using transactions will be necessary. A site doesn't need to be high volume to get a concurrency issue. The timing just needs to be right. So there's less probability in a low traffic site, but all the same, the problem produced as a result is the same. In a low volume site using a lock on the table is a simple method to prevent the problem since you can expect it will have minimal impact on the site in general (due to low volume). You only need the lock when you go to add the row... lock table check enrolment count no room unlock table generate error have room insert row unlock table Ba da boom. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Foreign Keys Question
On Thu, 2008-12-11 at 16:24 -0500, Robert Cummings wrote: On Thu, 2008-12-11 at 16:17 -0500, tedd wrote: At 3:14 PM -0500 12/11/08, Andrew Ballard wrote: That's the way to go. Just keep in mind that you will have to consider ways of preventing overloading your courses due to concurrency, whether you use table locks or devise your own mechanism. I don't know if you are allowing students to self-enroll or not. You just don't want two users to both see that a course has one opening left and both try to take that spot at the same time. Andrew It's very low volume at the moment, so I don't think there will be an immediate problem. However, I will investigate locking the tables for entry. But I don't think using transactions will be necessary. A site doesn't need to be high volume to get a concurrency issue. The timing just needs to be right. So there's less probability in a low traffic site, but all the same, the problem produced as a result is the same. In a low volume site using a lock on the table is a simple method to prevent the problem since you can expect it will have minimal impact on the site in general (due to low volume). You only need the lock when you go to add the row... lock table check enrolment count no room unlock table generate error have room insert row unlock table Ba da boom. I should have read your message better... you were talking about skipping transactions and not locking :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Foreign Keys Question
As a side note, FKs do enforce other table specific properties like indexes on the fields being constrained, so they do add value there as well. But there's of course an extra cost on updates and inserts to see if the FK is violated. Waynn On 12/11/08, Colin Guthrie gm...@colin.guthr.ie wrote: 'Twas brillig, and Chris at 12/12/08 01:20 did gyre and gimble: Micah Gersten wrote: Colin Guthrie wrote: The ON DELETE CASCADE option is key here... DELETE FROM students where student_id=1 will remove all traces of that student from the db... all the course they've attended, all the instructors who have taught them etc. keeps things nice and tidy without having to put the structure in your code all over the place. Col Why would you want to delete the instructors when deleting the student? I think he meant the link between the student instructor (in the student_instructor table), not the instructor itself. lol, indeed, that's what I meant... Sorry I thought it was implied in the context! Say you have the following layouts instructors: instructor_id, name students: student_id, name instructor_students: instructor_id, student_id This structure would hold a list of instructors and a list of studends and also a one to many mapping of instructors to students. If you delete a student the FK can cascade to the instructor_students table and thus delete the records that indicate a given instructor (or instructors) taught them. Col -- Colin Guthrie gmane(at)colin.guthr.ie http://colin.guthr.ie/ Day Job: Tribalogic Limited [http://www.tribalogic.net/] Open Source: Mandriva Linux Contributor [http://www.mandriva.com/] PulseAudio Hacker [http://www.pulseaudio.org/] Trac Hacker [http://trac.edgewall.org/] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Foreign Keys Question
Waynn Lue wrote: As a side note, FKs do enforce other table specific properties like indexes on the fields being constrained, so they do add value there as well. But there's of course an extra cost on updates and inserts to see if the FK is violated. On the external table? No they don't. mysql create table t1(id int primary key, name varchar(255)) engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql create table t2(t2id int, t1id int references t1(id)) engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql show create table t2\G *** 1. row *** Table: t2 Create Table: CREATE TABLE `t2` ( `t2id` int(11) default NULL, `t1id` int(11) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) No auto-index on t2(t1id) at all. You have to define that yourself - you might want it part of a multi-column index for example. You definitely should index it, but it won't happen automatically. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
