Re: pro- foreign key propaganda?
- From: "sinister" <sinister@xxxxxxxxxxxxxx>
- Date: Thu, 15 May 2008 15:28:48 -0400
"Bob Badour" <bbadour@xxxxxxxxxxxxxxxx> wrote in message
I'm supervising the development of a database system with a web
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
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.
- Prev by Date: Re: pro- foreign key propaganda?
- Next by Date: Re: pro- foreign key propaganda?
- Previous by thread: Re: pro- foreign key propaganda?
- Next by thread: Re: pro- foreign key propaganda?