[AccessD] 2 Questions

Hi Rocky:

Just a note; All the POS and accounting systems I have put together, in the
last 10 to 15 years do not delete records, they are just turned off the
records so they are no longer included or displayed. 

Every record is marked with a transaction code which is just a unique
data-time value.

There has been so many times when clients have had to recover their lost
clients, transactions, needed inventory tracking, track errors in the
system, save themselves from employee (and their own) errors, have to
perform forensic on their books or even catch employees trying to steal.
Legal accounting systems can not delete records and I just extended that
rule to the entire application.

The one draw back of course is that a client will have to be aware of this
when attempting adhoc queries against the data or when the data starts to
grow past 10 years. It demands a SQL BE or I have to start splitting the
archived data...

Jim

  

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Wednesday, March 10, 2010 6:06 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] 2 Questions

Cascade delete has it's risks as you point out - but I find it very useful
from the user's side for certain issues like deleting a purchase order or a
sales order where, without cascade delete,  the user has to delete each
detail record from the P.O. or S.O. before they can delete the header
record.  

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, March 10, 2010 5:35 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] 2 Questions

Tony,

Referential integrity is the foundation of a database.  Without it you are
hopelessly lost.

1) Referential integrity essentially says that you can't have a child
without having a parent (makes sense right?).

So... If you have clients and orders, you can't have an order without having
a client.  More importantly, you can't have an order without selecting a
SPECIFIC client.  Referential integrity says that you can't DELETE a client
without deleting all of that client's orders.  Again, makes sense right?  If
you are allowed to delete clients without deleting that client's orders then
you have orders with no clients (see one above).  This just hoses
everything.  You do reports of all your orders and where they shipped to...
ooops these orders don't have a client so we can't tell where we shipped
them to.  We count orders not paid for yet.  Ooops we have orders with no
client so there is no way to determine that they were paid for.  ANYTHING
that you have to have the Client record to report on gets trashed.

It's just a bad thing, having orders with no client.  Or any other child
record without a parent.

This condition is called "orphaned records" because a child with no parent
is an orphan.  Orphans are "lost", they just cause a raft of issues, all of
them BAD!

Referential integrity is the business of the database engine, NOT the FE or
application.  Every database engine, whether Jet, SQL Server, Oracle or any
other has powerful mechanisms built in for maintaining referential
integrity.

I mentioned that RE is not the business of the FE, and the reason is simple.
If you have to handle RE then you are CONSTANTLY checking in code whether
you performed all of the steps necessary to maintain RE.  Furthermore, you
place that same burden on any other developer accessing your data. 
Suddenly ANY developer trying to update, add or delete records has to be
concerned with RE.  Most of us developers simply are not trained to do RE
right, and even if we are, human error creeps in. 
Even worse, I EXPECT that you will use RE, EVERYONE USES RE.  So if you
don't, and I expect that you are, I don't bother to even attempt to handle
RE issues and suddenly I am trashing data.

BELIEVE ME (and the other developers on the list should chime in), RE is the
business of the database engine, NOT the developer!

Creating the relationships in the relationship window is how you turn on RE.

1) Open your relationship window.  Click, drag and drop from the PK in the
parent to the FK in the child.  An "edit relationships" dialog opens.  The
top check box underneath says "Enforce referential integrity.  Check that
box.  THAT is what causes JET to perform all of the checks to prevent
ORPHANS.

2) The next check says "cascade update...".  This has to do with cascading
updates to the data in the PK down into the child.  If you use autonumbers
(surrogate keys) for PKs (and you should), then the PK is never updated and
you do not need to check this box.  If you use natural keys, then the PK
data can and will change, and those changes have to be rippled down into the
child FKs.  Cascade update causes JET to do that "cascade" update to the
child FK fields.

3) The last check box says "Cascade delete...".  This has to do with whether
you want the database to automatically delete child records if the parent
record is deleted.  Like everything else, this is fiercely debated but my
opinion is that this is a BAD idea. The reason is simply that users tend to
ignore "are you sure" messages.  The more junior the user, the more they
ignore the "are you sure" messages.  Cascade delete on means that if the
user accidentally deletes that client record, the orders will just be
automatically deleted.  The checks, invoices, communications, whatever else
you have child to client will be automatically deleted.  With one simple
delete of one teeny little record you can rip huge chunks of your database
out.

It is a PITA but my method of handling this is to have a delete button which
only specific groups (supervisors / managers etc) can even see.  That button
does the "are you sure" prompt, then runs delete queries which delete the
child records in the correct order back up to the parent.

Again, this is fiercely debated but how you handle it is less important than
that you UNDERSTAND what this stuff does and why you would or would not use
it.  After that, it is on your head.

John W. Colby
www.ColbyConsulting.com


Tony Septav wrote:
> Hey Asger
> I am not trying to start a debate. But if I do my joins in a query 
> (with many many tables joined), if  I visually check my results  and 
> find an error (or add code to test for errors), I can strip everything 
> down  and rebuild it back up step by step.  This way I can verify my 
> results and ensure referential integrity. If  1 does not equal 1 then I am
in trouble.
> 
> Asger Blond wrote:
> 
>> 2. I use Tools | Relationship to create foreign key constraints ("enforce
referential integrity"). Without foreign key constraints the data will soon
loose consistency.
>>
>> Asger

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.733 / Virus Database: 270.14.129/2605 - Release Date: 03/08/10
23:33:00

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com