Re: pro- foreign key propaganda?
Date: Thu, 15 May 2008 17:53:13 -0300
Message-ID: <482ca2be$0$4047$9a566e8b_at_news.aliant.net>
sinister wrote:
> "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?
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.
Using an end-user query tool as if it were a dbms is a recipe for disaster. Received on Thu May 15 2008 - 22:53:13 CEST