Rocky Smolin
rockysmolin at bchacc.com
Wed Mar 10 09:28:16 CST 2010
I routinely put login name and password in an app and each user has read
only, read/write, or administrator access. So it's easy to restrict
specific functions to admin levels. However, most of my clients are small -
2-6 users, and I have not had anyone call up crying in their beer about
delete errors.
I did create an app recently to track and report a complex royalty
arrangement for a company that sells software to protect movie rights in
devices like blu-ray players. Auditing capability is very important to
them.
For that one I used your approach of never actually deleting a record but
marking it as deleted in the database so it 'disappears' from all the forms
and reports. But no bridges are burned. All their deleted data is
recoverable. They liked that idea.
But I also give my clients a lot of advice about back ups and ask them from
time to time if they're doing it. So, should someone delete a record by
mistake, they can always roll back to the most recent backup.
I did have a user years ago that discovered queries. Shortly after that,
large amounts of data were disappearing for no reason. Turns out, she was
running a query that would retrieve a large number of records, amongst which
she was interested in only half a dozen. SO she deleted the ones from the
query that she didn't want and printed the rest - not knowing that when you
delete records from a query you're actually deleting them from the tables.
A little knowledge...
Rocky
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, March 10, 2010 6:57 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] SPAM-LOW: Re: 2 Questions
Rocky,
I understand your point. My point is simply that the average user has no
business deleting records in the database. In general this should be a
supervisor (or higher) decision. A button which runs a series of delete
queries does the same thing as "individually deleting the child records".
Whether you use a button simply forces the user to think a little more about
the delete. If it is hidden unless you are a supervisor completely removes
the issue from the average user. How many times have you hit the delete key
by accident, then you are not looking at the keyboard and hit enter. The
"ARE YOU SURE" message may have just been answered affirmatively. Either
way, I have seen too many users just click "YES", muttering under their
breath "of course I'm sure" and they haven't even read what it is they are
saying yes to.
If you have specific instances where cascade deletes are useful then fine.
Making that the DEFAULT is bad practice (IMHO).
John W. Colby
www.ColbyConsulting.com
Rocky Smolin wrote:
> 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
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/09/10
23:33:00