Re: pro- foreign key propaganda?


"Bob Badour" <bbadour@xxxxxxxxxxxxxxxx> wrote in message
news:482c3e85$0$4046$9a566e8b@xxxxxxxxxxxxxxxxxx
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.


.

Relevant Pages

  • Re: pro- foreign key propaganda?
    ... I had built the DB backend using postgresql. ... that foreign keys be used where possible, that as much of the model ... If he is that much of a newb, make sure he is not using MyISAM. ... didn't declare any integrity because he doesn't think mysql will enforce ...
    (comp.databases.theory)
  • Re: pro- foreign key propaganda?
    ... I had built the DB backend using postgresql. ... 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. ... 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. ...
    (comp.databases.theory)
  • Re: [PHP] Foreign Key Problem
    ... I am useing mysqlAdmin to ... In this version of Mysql I am unable to set foreign key ... Make sure you are using a MySQL storage engine that supports referential ... MyISAM does not, but Innodb does. ...
    (php.general)
  • Re: Poor mysql scaling across the board pre 5.1.22?
    ... mysql 5.0 has poor scaling compared to postgresql (this affects ... reads but severely affects writes). ... for MYISAM tables, I found that to be a write performance killer with ...
    (freebsd-performance)
  • Re: pro- foreign key propaganda?
    ... reasons decided to switch to MySQL and created his own tables (as well as ... he shoulnd't have redone the database at all. ... connected by a foreign key relationship, I went and checked his DB; ... There was alot of pain coming our way from the customer ...
    (comp.databases.theory)