Cascade Delete / Referential Integrity

[Jared Sutherland]

Hey everyone,

I am working on a client information database and am attempting to use as much XBasic (instead of Action Scripting) as possible, as I have found a lot of the Action Scripting to be too slow and too general. I have been told by multiple users on this board to NOT use any Referential Integrity that can be used with Sets, as it can cause problems as users / data storage increases.

My question is, are there any recommendations anyone can give to cascade delete/changes information in my records. I.E. I have a table named Policy, each policy has a manually entered Policy Number (which are unique), each policy will have employee's attached to it. Right now, if I change the Policy Number in the Policy Form, the employees attached to it will drop off, as the change does not cascade to the connected employees. Is there anyway to avoid this or fix this issue besides not allowing changes to this field?

[martinwcole]

RE: Cascade Delete / Referential Integrity

when you turn off referential integrity, as is recommended for LANS, you have to deal with orphans.

You can do it on a batch basis, like from a button on a utilites menu, or when the adb closes. Or you can do it by trapping the event at each occurence.

The batch is by far the easiest. But if you are in a development mode, continually adding and changing things, you need to methodically keep track of the files that need to be deleted and the condition. Then you can create a series of operations that will remove children with no valid parent.

If you do it at the time of deletion, you almost always have to deal with it in field rules in the record event ondeleterecord, since people will typically delete records in embedded browses and browses with the Ctrl-D.

[russ Boehle]

RE: Cascade Delete / Referential Integrity

Another way to look at this is to not create dependencies that will be invalid in the future. In you case, rather than linking on policy number you could link on client name and have the employees as children of the parent client. You could then also have the policy number as a child of client(link on client name or ID). If you delete a policy it does not change the relationship of client to employee so you don't have the cascading delete problem.

Russ

[Jim Chapman]

RE: Cascade Delete / Referential Integrity

Hello Jared,

A couple of thoughts for you. Referential Integrity is usually only an issue on a networked app. The issue is it can cause a lot of record locking depending on your structures. It is quite easy to create a routine that will delete your orphans.

A thought on linking values. It is my opinion that a user entered value should NEVER be used as a linking value (Of course there are always exceptions but these are mostly utility in nature). Allow the machine to create this linking value either with an autoincrement field rule or your own routine. The linking value should be divorsed from any meaningful data. This way typo's and routine changes in data will have no effect on the linking.

Good luck,
Jim

[Jared Sutherland]

RE: Cascade Delete / Referential Integrity

Jim that is a good point, I usually feel the same way on the issue, however, because I already know that Policy numbers are going to be unique, they are ideal for a linking value, but they are also user entered and can be modified... I suppose I could use a "behind the scenes" key to link them, which may be the best route...

Why does referential integrity cause problems on LANs? Is it because of the possibility that two people may be accessing / changing the same record? My app is going to be both used on a LAN (not by more than 2-3 simultaneous users) and also it will be accessed through a data driven webpage.

[martinwcole]

RE: Cascade Delete / Referential Integrity

With that few users you could try it with referential integrity turned on. But the child records will be locked.
In other words, if you are editing the parent or any child, all records for that parent will be unavailable to others to edit. But that may not be an issue. One reason why developers typically turn it off is that it reduces traffic, and thus enhances performance. If you leave it on, then as you enter/change the policy number it will update the child policy numbers - but, like Jim, I really do not recommend the approach of letting users enter linking keys.

if you need the operator to enter and modify the policy number - then don't use the policy number as the linking key.

Instead, add a unique field to the parent, that is either autoincrement or a roll-your-own method(my personal preference) - then add that same field to the child. Now, regardless, the correct child is connected to the correct parent - however, now you have to deal with updating the child policy number - which is no big deal, and would be easy via xbasic - like at the cansave event of the form.

[Jim Chapman]

RE: Cascade Delete / Referential Integrity

Hello Jared,

""I suppose I could use a "behind the scenes" key to link them,""
Absolutely, the link really shouldn't need to be view by users, again, it should be divorsed from any 'meaningful' data. Let the machine generate it, you'll be better off in the long run.

""Why does referential integrity cause problems on LANs?""

With referential integrity turned on, Alpha has to 'protect' the links. As such whenever you are in a parent record in edit mode, it must lock all the child/grandchild/etc records 'down stream' on the potential that you may change the linking value. As a single parent record can potentially be linked to many child/grandchild records, several instances of a given set could be opened for any given user, and multiple users could also be in the set, the cascading locks through the tables can cause overhead and access problems.

The old KISS formula applies. The more users you will have on the system, the more you want to pay attention to these issues.

Good luck,
Jim