soledad penadés

It seems like some people is not aware yet of the fact that with mysql you can have not only transactions but also foreign key constrains, apart from other features which were usually found only on expensive products like Oracle, MSSQL server, etc...

The problem is that as these features weren't available until some months/years ago, there's a vast amount of php software which does not use them, and hence php developers [which read/use other developers code] do not know about. So all majority of code examples and real-life applications do not take care of adding constraints or transactions in the SQL data model declaration. Instead, they do all the checkings and validations in the php code. And that's also what I do.

Is it right? I wouldn't say yes, but I wouldn't say no. My workmate is completely obsessed with foreign keys, he can't stop adding those constraints to every pair of tables that are linked... and he also can't stop complaining about me not setting those constraints when declaring the tables. Why do I do that?

Mainly for convenience. In this world of undecided clients and blurred targets, we are going to possibly mess up with the database tables lots of times, at least at the beginning of the project when the data model tends to not to be very well defined. Also, for having foreign keys and transactions and all that funky stuff, your tables need to be InnoDB, which are said to be slower than MyIsam (which are the default ones that mysql uses when you do not indicate any table type during table creation), mainly when accecsing data in read only mode, not updating or inserting it - and that's how websites works, don't they?

Sure, we all have been taught that a good data model must be consistent and preserve data integrity as one of the main goals (apart from storing data in itself, of course!). One could argue that foreign keys are data in themselves, as they mean something; you can't simply remove them without losing information somehow. But as I said before, during development I think it's going to be harder if you are playing with the tables and the data inside them. Lots of integrity constraint failed errors, lots of lost time because of that.

I am thinking of a compromise solution: not using foreign keys during development but adding them when everything is almost definitive (where everything means the data model, etc...). As we also make checks in our business layer (I hate speaking with that terminology!), we can't lose any functionality. And if there was an error, it will show!
I would love to listen any suggestion/opinion/truth/whatever. Please take into account that I am approaching the foreign key issue from a practical point of view; I would prefer to avoid metaphilosophical discussions about the database designs. Or at least I'll avoid those which are not human readable. Thanks!