Re: pro- foreign key propaganda?

"Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message news:482c3e85$0$4046$9a566e8b_at_news.aliant.net...
> sinister wrote:
>> I'm supervising the development of a database system with a web
>> interface.
>>
>> I had built the DB backend using postgresql. The guy we hired to take
>> over the project from me---I have non-IT duties to attend to---for
>> various reasons decided to switch to MySQL and created his own tables (as
>> well as redoing the web interface).
>>
>> After a conversation involving two tables which I thought ought to be
>> connected by a foreign key relationship, I went and checked his DB; I was
>> worried that he didn't fully understand the importance of using foreign
>> keys. So I ran mysqldump and then did "grep -i foreign dump.sql" and
>> "grep -i references dump.sql." Nothing!
>>
>> So...sent him an email late in the day, stressing that it's important
>> that foreign keys be used where possible, that as much of the model
>> logic/business logic/whatever should be encoded in the database itself to
>> ensure data integrity, and so on.
>>
>> What do I do if he gets back to me and says---as I'm sure everyone in his
>> position does---"oh, but my PHP code makes sure everything is done
>> right"? Not that I don't know what to say, but it would be good to have a
>> pointer to an essay or webpage that succinctly explains why you're asking
>> for trouble if you don't encode such relationships in the DB itself.
>>
>> Sure, I could say, "Your next assignment is to read a book on DB theory,
>> and give me a 10 minute summary," but we don't have that much time...
>>
>> :-(
>
> If he is that much of a newb, make sure he is not using MyISAM. Maybe he
> didn't declare any integrity because he doesn't think mysql will enforce
> it in any case. But MyISAM can (will?) corrupt your data.

I actually told him at the beginning to use InnoDB---because MyISAM doesn't enforce fk constraints---and thankfully enough he did do that.

MyISAM and corruption---due to lack of constraint enforcement, or because it's buggy?

In the previous version of the project we're working on, the guy (not the same one) was using Microsoft Access. A pretty important table got corrupted, in the sense that for about 20% of the rows, half of the row really belong with another row. (Meaning, there's a name and a record number assigned by another dept, and in those 20%, the rec num wasn't just incorrect, it belonged to another name in our table, even though we have about 2000 names, and the dept providing the rec num has at least tens of thousands, so it can't be just wrong randomly.) There was another data source in the form of an Excel table (this is when I was trying to clean up the data and put it in a nice PostgreSQL table), and they conflicted, and I figured, "OK, Access isn't my favorite DB, but surely it must be because someone took the Excel table and shifted some cells up and 'broke' the rows." Turns out Access was wrong! I have no idea how _that_ happened. Received on Thu May 15 2008 - 21:28:48 CEST