Re: Foreign Keys Constraints, perforamance analysis - Mailing list pgsql-general

Daniel Åkerud wrote:
>
> OK, I've been discussing this with a collegue of mine... and I'm starting to
> see the light here...
>
> I will, first of all, make a new, simpler, 1<->1 realtionship to test FK
> constraints... no 2<->1<->2 relasionship here...

    One  thing  you'd  have to take into account is the fact that
    the MySQL documentation is totally misleading. If you  intend
    to  compare  apples to apples, you'd have to take a more real
    world scenario.

    Every business application has to reflect the business  rules
    it  implements.   Using  a  relational  database  system it's
    possible to move some of the business logic into the database
    itself.  So  we  allways have to look at the middleware (e.g.
    PHP) and the database (e.g. Postgres)  as  a  unit.   If  the
    business modell now requires some referential integrity, like
    there can never be  an  invoice  referencing  a  non-existent
    customer,  this  unit has to ensure it, no matter what. There
    are two possible solutions,

    1.  Setup a foreign key constraint, so that the database will
        not allow the insertion of the invoice or deletion of the
        customer, however concurrent the DB access might be.

    2.  Implement the required checks  with  appropriate  locking
        and   transaction   coverage   at   every  place  in  the
        application, where these two relations are  modified  WRT
        the logical requirement of the business model.

    IMHO  solution #1 has a major advantage. Only the DB designer
    really must understand the entire business modell  to  ensure
    that  there  will never be logically inconsistent data in the
    database. The worst thing that can happen if a WEB  developer
    doesn't  honor  the business modell is an aborted transaction
    and maybe an error message the user doesn't  understand.  For
    #2  every  WEB  programmer,  at any time editing a single PHP
    code snippet, has to have the business modell in  mind.  Here
    the  worst  possible consequence is violation of the business
    modell. So we might have invoices where we don't know anymore
    who's the customer.

    Thus,   to   compare   MySQL  vs.  Postgres  WRT  referential
    integrity, create a sample  application  where  the  Postgres
    version  checks  for  possible errors (can be optimized using
    deferred constraints and checking just at  the  COMMIT).  The
    MySQL  version  instead  implements  the  constraints  on the
    middleware level including transaction handling and  locking,
    so you'd have to use BDB tables only for example.

    Just my $0.02


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com