RE: [PHP] Re: Foreign Keys Question
-Original Message- From: clive [mailto:clive_li...@immigrationunit.com] Sent: Friday, December 12, 2008 1:07 AM To: PHP LIST Subject: Re: [PHP] Re: Foreign Keys Question Colin Guthrie wrote: 'Twas brillig, and tedd at 11/12/08 18:46 did gyre and gimble: 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. Well that's a fairly significant service in itself. The whole deleting data case is where FK's have saved me significant amount of coding. 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 Is it just me or does anyone else here not like deleting from a database, I normally have a status field to indicated if a row has been deleted. What about historical data, would you not want to know that studentX was enrolled at some point in the past, if you just delete that student and all related data how would you know this? You could also have a 2nd database with the same table structure and move old/delete data into there. You are describing a data warehouse, or a data mart. That is not what transactional databases are there for. Make a historical database, and make a transactional database... but don't make one that tries to be both, or you're just shooting yourself in the foot. // Todd
[PHP] Re: Foreign Keys Question
At 10:24 PM + 12/11/08, Colin Guthrie wrote: 'Twas brillig, and tedd at 11/12/08 18:46 did gyre and gimble: 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. Well that's a fairly significant service in itself. The whole deleting data case is where FK's have saved me significant amount of coding. 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 Col: That's neat and a lot more powerful than I thought. It's like following a linked list to it's end while removing all traces of the thread. And I understand the instructor delete was not intended. 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] Re: Foreign Keys Question
'Twas brillig, and tedd at 12/12/08 14:36 did gyre and gimble: That's neat and a lot more powerful than I thought. It's like following a linked list to it's end while removing all traces of the thread. Exactly And I understand the instructor delete was not intended. Yeah indeed. I had a db structure in my head and the statement made vague sense with that in mind, but it totally failed to leak through my hands on to the keyboard :P There are three main options here: * ON DELETE CASCADE (if the FK's referenced table has it's record deleted, delete the record here too). * ON DELETE RESTRICT (if the FK's referenced table has it's record deleted stop that whole transaction - e.g. *prevent* the delete). * ON DELETE SET NULL (if the FK's referenced table has it's record deleted, set this tables reference to NULL). All three are useful in different contexts. I use them extensively to ensure good data integrity. The trade off on extra load on insert/update is IMO well worth it. 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] Re: Foreign Keys Question
At 2:50 PM + 12/12/08, Colin Guthrie wrote: 'Twas brillig, and tedd at 12/12/08 14:36 did gyre and gimble: That's neat and a lot more powerful than I thought. It's like following a linked list to it's end while removing all traces of the thread. Exactly And I understand the instructor delete was not intended. Yeah indeed. I had a db structure in my head and the statement made vague sense with that in mind, but it totally failed to leak through my hands on to the keyboard :P There are three main options here: * ON DELETE CASCADE (if the FK's referenced table has it's record deleted, delete the record here too). * ON DELETE RESTRICT (if the FK's referenced table has it's record deleted stop that whole transaction - e.g. *prevent* the delete). * ON DELETE SET NULL (if the FK's referenced table has it's record deleted, set this tables reference to NULL). All three are useful in different contexts. I use them extensively to ensure good data integrity. The trade off on extra load on insert/update is IMO well worth it. Col Col: That's all good to know. My first tendency is to keep everything. After all, memory is cheap and access times are always reducing. While it's true that having a bunch of worthless data doesn't accomplish anything and slows the process of dealing with it. But, technology in access times and storage capabilities are getting to the point of making the decision to keep/delete worthless data moot. As such, I think the need for FK deletions will become less and perhaps disappear from the language. For some reason, I look upon deletions in similar light as renumbering a table's index after deletion of a record -- like what's the point? I'm just rambling -- thanks again for your insight. 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] Re: Foreign Keys Question
In my enrollment database, we keep historical information. My client is a big computer training organization (for which I also teach). The enrollment information is quite fluid -- people are always canceling or rescheduling. It is important for us to know a person's history. $.02 Ken On Dec 12, 2008, at 9:16 AM, tedd wrote: At 2:50 PM + 12/12/08, Colin Guthrie wrote: 'Twas brillig, and tedd at 12/12/08 14:36 did gyre and gimble: That's neat and a lot more powerful than I thought. It's like following a linked list to it's end while removing all traces of the thread. Exactly And I understand the instructor delete was not intended. Yeah indeed. I had a db structure in my head and the statement made vague sense with that in mind, but it totally failed to leak through my hands on to the keyboard :P There are three main options here: * ON DELETE CASCADE (if the FK's referenced table has it's record deleted, delete the record here too). * ON DELETE RESTRICT (if the FK's referenced table has it's record deleted stop that whole transaction - e.g. *prevent* the delete). * ON DELETE SET NULL (if the FK's referenced table has it's record deleted, set this tables reference to NULL). All three are useful in different contexts. I use them extensively to ensure good data integrity. The trade off on extra load on insert/update is IMO well worth it. Col Col: That's all good to know. My first tendency is to keep everything. After all, memory is cheap and access times are always reducing. While it's true that having a bunch of worthless data doesn't accomplish anything and slows the process of dealing with it. But, technology in access times and storage capabilities are getting to the point of making the decision to keep/delete worthless data moot. As such, I think the need for FK deletions will become less and perhaps disappear from the language. For some reason, I look upon deletions in similar light as renumbering a table's index after deletion of a record -- like what's the point? I'm just rambling -- thanks again for your insight. 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Foreign Keys Question
'Twas brillig, and tedd at 12/12/08 15:16 did gyre and gimble: My first tendency is to keep everything. After all, memory is cheap and access times are always reducing. While it's true that having a bunch of worthless data doesn't accomplish anything and slows the process of dealing with it. But, technology in access times and storage capabilities are getting to the point of making the decision to keep/delete worthless data moot. As such, I think the need for FK deletions will become less and perhaps disappear from the language. For some reason, I look upon deletions in similar light as renumbering a table's index after deletion of a record -- like what's the point? I'm just rambling -- thanks again for your insight. Rambling is good... I'll continue! With data retention and data protection laws (something that can vary around the world making life for web-based providers like ourselves even more complex), I think it is increasingly important that information about a given person can be scrubbed very easily. Keeping the data may be cheap from a storage/access perspective, but complying with laws and regulations can be wearisome and time consuming. If you FKs are fully up-to-date and have proper cascading you can be sure that a simple: DELETE FROM users WHERE user_id=123; really will delete all the information you store about that individual. You just have to look at the hullabaloo over the deactivated Facebook accounts etc. to realise that hiding or disabling data is not enough in many cases. Food for thought! 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] Re: Foreign Keys Question
At 4:34 PM + 12/12/08, Colin Guthrie wrote: 'Twas brillig, and tedd at 12/12/08 15:16 did gyre and gimble: My first tendency is to keep everything. After all, memory is cheap and access times are always reducing. While it's true that having a bunch of worthless data doesn't accomplish anything and slows the process of dealing with it. But, technology in access times and storage capabilities are getting to the point of making the decision to keep/delete worthless data moot. As such, I think the need for FK deletions will become less and perhaps disappear from the language. For some reason, I look upon deletions in similar light as renumbering a table's index after deletion of a record -- like what's the point? I'm just rambling -- thanks again for your insight. Rambling is good... I'll continue! With data retention and data protection laws (something that can vary around the world making life for web-based providers like ourselves even more complex), I think it is increasingly important that information about a given person can be scrubbed very easily. Keeping the data may be cheap from a storage/access perspective, but complying with laws and regulations can be wearisome and time consuming. If you FKs are fully up-to-date and have proper cascading you can be sure that a simple: DELETE FROM users WHERE user_id=123; really will delete all the information you store about that individual. You just have to look at the hullabaloo over the deactivated Facebook accounts etc. to realise that hiding or disabling data is not enough in many cases. Food for thought! Col Col: I'll continue rambling. Excellent point -- Yes, I forgot about security issues. For example most on-line credit card processing agreements state that you must delete the actual credit card information (i.e., cc and cvs numbers) within 24 hours of a customer's purchase. I often have problems explaining that to clients who want to keep such information on-line. I tell them what they do with the hard copy of that information is their business, but I'm not going to jail to keep that data in a database for them. Anyone else experienced that problem? And if so, how did you handle it? 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
[PHP] Re: Foreign Keys Question
'Twas brillig, and tedd at 11/12/08 18:46 did gyre and gimble: 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. Well that's a fairly significant service in itself. The whole deleting data case is where FK's have saved me significant amount of coding. 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 -- 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
Re: [PHP] Re: Foreign Keys Question
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? Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Foreign Keys Question
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. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Foreign Keys Question
'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
Re: [PHP] Re: Foreign Keys Question
Colin Guthrie wrote: 'Twas brillig, and tedd at 11/12/08 18:46 did gyre and gimble: 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. Well that's a fairly significant service in itself. The whole deleting data case is where FK's have saved me significant amount of coding. 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 Is it just me or does anyone else here not like deleting from a database, I normally have a status field to indicated if a row has been deleted. What about historical data, would you not want to know that studentX was enrolled at some point in the past, if you just delete that student and all related data how would you know this? You could also have a 2nd database with the same table structure and move old/delete data into there. Clive -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Foreign Keys Question
On Fri, 2008-12-12 at 09:06 +0200, clive wrote: Colin Guthrie wrote: 'Twas brillig, and tedd at 11/12/08 18:46 did gyre and gimble: 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. Well that's a fairly significant service in itself. The whole deleting data case is where FK's have saved me significant amount of coding. 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 Is it just me or does anyone else here not like deleting from a database, I normally have a status field to indicated if a row has been deleted. What about historical data, would you not want to know that studentX was enrolled at some point in the past, if you just delete that student and all related data how would you know this? You could also have a 2nd database with the same table structure and move old/delete data into there. It depends on the data. Certainly for student enrolments I would want a paper trail (so to speak) and would just set a status field. But if it was say, an online shopping cart or cached data... I'd just purge it. 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] Re: Foreign Keys Question
Robert Cummings wrote: It depends on the data. Certainly for student enrolments I would want a paper trail (so to speak) and would just set a status field. But if it was say, an online shopping cart or cached data... I'd just purge it. Cheers, Rob. yes - you are right, I was just thinking about the student enrolment scenario :-) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
