Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    18

    Integrity on large sites

    I'm working in a project at the moment that is using MySQL, and people keep making assertions like this one:

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

    A large DB working with no RI would give me nightmares. Is it really true that large sites turn RI off to improve performance? Am I just being naive in thinking that everyone runs their DBs with RI in production?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    No. Don't talk to those people any more. They are idiots.


    It is not true that really big databases do not use referential integrity.
    What is true is that really bad databases do not use referential integrity, and some of them happen to be big databases.

    And implementing referential integrity at the application level is a recipe for disaster.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    come on, blindman, don't hold back, tell us how you really feel

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by naz
    "*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."
    naz, my reply to two statements like these would be "What a MORONIC thing to say...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    If you never want to store data correctly and never want to retrieve data correctly go ahead and turn off referential integrity. To turn off referential integrity is the removal of all the business rules from you business data. To bury it in applications decentralizes control and documentation of the business rules and ultimately leads to conflicts that create corruption.

  6. #6
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by naz
    "*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."
    The best response to something like this is to throw what they just said back at them.

    "So the big sites *do* have referential integrity, but it's not documented in a single place and it's applied inconsistently. Thus we get the worst of both worlds: all the potential drawbacks of referential integrity (having to update in a certain order, locking and unlocking) with none of the guaranteed benefits."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •