Re: Integrity on large sites

On May 23, 2007, at 12:54 PM, Scott Ribe wrote:

>> "*Really* big sites don't ever have referential integrity. Or if
>> the few
>> spots they do (like with financial transactions) it's implemented
>> on the
>> application level (via, say, optimistic locking), never the
>> database level."
>
> Pure, utter, unadulterated bullshit. Speaking as someone who had
> years of
> experience with Sybase SQL Server before either MySQL or PostgreSQL
> were
> even created...
>
> Some big sites do of course juggle performance vs in-database run-time
> checks, but the statements as typically presented by MySQL
> partisans, that
> it's never done in the database level, is just wrong. Whether it's
> a direct
> deception, iow speaker knows it to be false, or an indirect
> deception, iow
> speaker is implying a level of expertise he does not possess,
> either way I
> would categorize it as a lie.

I concur with the claim of organic fertilizer.

I got into a rather spicy argument at the only RAILS conference I
went to. They have this mentality that with Rails you don't need to
put in RI on the database because you can always run exists? checks
right before you do the insert to ensure integrity of your data. Not
only does this apply to Referential Integrity, but also unique
values. I was damn near screaming at them over the stupidity of such
a notion.

My experience is based on working at a rather large company that has
a really huge Oracle database.
When they designed it, they passed up on all Referential integrity
and all unique constraints.
After five years, we have tables that are >60% duplicate records and
the database is coming to a standstill.
And there is no known method in sight on being able to fix this one.

Bottom line, if the DBA or anyone says we can't support RI or UNIQUE
because of the performance overhead... I would be inclined to look
for another DBA.
But I have to admit. I am extremely opinionated about this as I'm
the guy who does most of the performance and metric reporting using
these horrid tables.
it does provide infinite job security, but it's hardly worth it in
the long run.