~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <markp@g...>
Sent: Thursday, June 27, 2002 5:39 AM
Subject: [access_asp] Re: Relationships in Access 2000 - are they worth it?


: Darrell, I'm not sure that's actually the case - I have come across at
least
: one situation where the 'best' solution to the display of data from
mutliple
: tables on a single form was to generate a view based on a (highly
: denormalised) table that was built (overnight) from a whole series of
: related tables. Creating a view based on the normalised tables just was
not
: feasible given the response times required. On the whole relational
database
: ARE slow when compared to say hierarchical or OO databases.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

When selecting data out of a database, it can sometimes be faster to
eliminate joins by denormalising tables. Typically this is used in OLAP
systems (online analytical processing systems), where a large number of
"what if" type scenarios need to be considered, or in ESS/DSS systems.

For OLTP (online transaction processing systems), which are what one uses to
power a typical application such as an e-commerce store, one gets faster
performance from a properly normalised data model. Why? One reason is
because you don't need to build a vast amount of business logic to combat
insert/update/deletion anomalies that occur when you have denormalised data.

Note: when you are only doing SELECT queries, you don't get
insert/update/deletion anomalies, hence the most prevalent use of
denormalised tables is in OLAP systems where you are generating reports/what
if scenarios.

Now, enforcing referential integrity, and normalising your data are two
related, but separate concepts. Whether your data is completely normalised,
or partly de-normalised needs to be performance based. Excessive JOINS (to
get data out) are expensive, and so you need to consider what your database
is being used for when designing the data structure.

Enforcing referential integrity is somewhat separate. Once you have decided
how normalised your database is, IMHO, you should (as a general rule) always
enforce referential integrity unless you have a good reason not to.
Otherwise, you need to write large amounts of code in your business or data
access layers to do the same work for you that the RDBMS is quite capable of
doing efficiently.

Whilst there is some kind of performance penalty for enforcement, I would
find it *very* hard to believe that you/me/we could write a data access
layer that would do this kind of job *more* efficiently that the DBMS can...

In any case, there are a number of experience DBAs on the SQL Language and
SQL Server lists that p2p.wrox.com has - perhaps we could take the
discussion over there to get some more opinions?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: The former are just plain awkward to maintain, whilst the latter
: seem to be the good idea no-one wants.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

OO databases are a "good idea" that "no one wants"? That's news to me :-)
Other than a few specialised applications, I don't see much demand for OO
databases for general applications.
<without starting a religious war>
They are simply too difficult to get working in the real world...
</without starting a religious war>

Cheers
Ken