Re: pro- foreign key propaganda?

"Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message news: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.

LOL! I agree.

Once I posted something like "Is Access a true RDMS?" on an access USENET group, and got a huge amount of shit for it.

:-) Received on Fri May 16 2008 - 02:56:39 CEST