RE: [PHP] Re: Foreign Keys Question

> -----Original Message----- > From: clive [mailto:clive_lists@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