Re: Foreign Keys Constraints, perforamance analysis

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(at)Yahoo(dot)com #

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