SEO

#1 (**permalink**) 04-09-2008, 05:23 PM

----- Original Message Follows -----
From: "Stuart Cooper" <stuart.cooper@gmail.com>

>> "*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."
>
>Some large sites don't even use data types!
>
>http://www.thestar.com/News/article/189175
>
>"in some cases the field for the social insurance number
>was instead filled in with a birth date."

But the fact that they don't use data types, or that some
big sites supposedly may not use referential integrity does
not provide justification that doing without is a Good
Thing. The Canadian Tax article, to any competent systems
admin, would provide incredibly strong justification FOR
using typed and validated data and referential integrity.
Anyone who has to be concerned with the integrity and
validity of their data, which should be the case everywhere
-- otherwise why bother collecting it -- has to enforce
those aspects, and RDBMS are built to do that. "Turning it
off" doesn't seem like a good way to address performance
issues. Buy bigger/better hardware and adjust configuration
settings. Data integrity has to be the first and fundamental
concern. Performance is irrelevant if you can't trust the
data -- would having answers faster be of any use if the
answers were not reliable?

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

#2 (**permalink**) 04-09-2008, 05:23 PM

>>> "*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."

Sure, but in the forum benchmark I just did, when using MyISAM, with no
reference integrity checks, at the end of the benchmark, there is an
impressive number of records with broken foreign key relations... when the
user kills his HTTP connection or reloads at the wrong moment, and the
script is interrupted, or killed by an exception or whatever, boom.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

#3 (**permalink**) 04-09-2008, 05:24 PM

PFC 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."
>
> Sure, but in the forum benchmark I just did, when using MyISAM, with
> no reference integrity checks, at the end of the benchmark, there is an
> impressive number of records with broken foreign key relations... when
> the user kills his HTTP connection or reloads at the wrong moment, and
> the script is interrupted, or killed by an exception or whatever, boom.

One assumes you mean implicit foreign key relations, since MyISAM doesn't
enforce them (hence the reason they're "broken", potentiated by the lack of
transaction support).

Sadly, there is a market for wrong answers faster.

--
Lew

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Re: Integrity on large sites