Thread: CMS, foreign keys, and the legacy of MySQL

I'm looking at a CMS written by someone for us (it's using Joomla).  Not only does his code not use foreign keys, but Joomla, at least the older version I'm looking at, doesn't.  MyISAM, of course.  What a PITA---I knew about the issue of referential integrity, but there's also an element of "self-documentation" that's missing if FKs aren't specified.
 
Then there were a couple seemingly core Joomla tables that appeared to violate some obvious database design principles re normalization.
 
How did it come that so many web developer types came to know so little about DBs?  I'm not a DBA, but a one-semester course on DBs is enough to leave me horrified.
 
Here's a comment I found while googling around on these issues:  "The problem isn't that InnoDB itself is slow, it's that enforcing foreign key relations is slow. I'm a big fan of referential integrity, but in a CMS it's place is in the application layer, not the data layer."  Why would anyone say that?  What's the point of using a DB if you're not doing to put FKs in it?

On 3/17/11 11:52 AM, jj ff wrote:
> Here's a comment I found while googling around on these issues:  "The
> problem isn't that InnoDB itself is slow, it's that enforcing foreign
> key relations is slow. I'm a big fan of referential integrity, but in a
> CMS it's place is in the application layer, not the data layer."  Why
> would anyone say that?  What's the point of using a DB if you're not
> doing to put FKs in it?

Well, they're right ... for InnoDB.  If you're only experience with a
relational database is MySQL, then you're liable to think that FKs are a
disaster.

--
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

On Thu, Mar 17, 2011 at 11:59 AM, Josh Berkus <josh@agliodbs.com> wrote:
> On 3/17/11 11:52 AM, jj ff wrote:
>> Here's a comment I found while googling around on these issues:  "The
>> problem isn't that InnoDB itself is slow, it's that enforcing foreign
>> key relations is slow. I'm a big fan of referential integrity, but in a
>> CMS it's place is in the application layer, not the data layer."  Why
>> would anyone say that?  What's the point of using a DB if you're not
>> doing to put FKs in it?
>
> Well, they're right ... for InnoDB.  If you're only experience with a
> relational database is MySQL, then you're liable to think that FKs are a
> disaster.
>
> --
>                                  -- Josh Berkus
>                                     PostgreSQL Experts Inc.
>                                     http://www.pgexperts.com
>

There are specific needs for doing things in unorthodox manners. If
the company is surviving by a thread and pulling fk means that the
company does not need to buy a new db server... yeah it may make
sense. Or if you operate in an environment where doing the check in
software is slightly more efficient and slightly more efficient means
saving a truckload of cash.

But yeah, most of the time fk's don't exist are because of ignorance
or lazy ignorance. Don't blame MySQL or InnoDB, they are pretty
awesome for a large set of possible problems.

If in doubt, blame a developer. <- You can quote me on that.


--
Rob Wultsch
wultsch@gmail.com

On Mar 17, 2011, at 11:52 AM, jj ff wrote:

> "The problem isn't that InnoDB itself is slow, it's that enforcing foreign key relations is slow. I'm a big fan of
referentialintegrity, but in a CMS it's place is in the application layer, not the data layer."  Why would anyone say
that?

If you view the DB as just a way of getting the application's data onto disk and off of it, then the quotation is not
anunreasonable entailment of that philosophy: The DB just stores stuff and returns it, and the application is the one
thatcares about the semantics of the data.  The logical conclusion of this is a schemaless database (CouchDB, etc.),
sinceit's all just blobs of application data. 

Given that (at a guess) 90% of web startups are staffed by relatively junior application programmers with no DB
background,and the natural tendency of very smart people to assume that which they do not understand isn't very
important,and there you are. 

Of course, as the application grows, and the number of different clients of the data grow, and you start getting data
problemsbecause the enforcement is distributed across tons of application code, the use of having data integrity in the
databasebecomes obvious.  Sadly, it usually requires something bad happening first, though. 

--
-- Christophe Pettus
   xof@thebuild.com