Cyclic Cascade Update? DB Design Question

[Shep]

Is this scenario considered bad design form? (see attached image)

I'll never delete a Customer or Site for whom a a Job exists. I need historical data to remain intact. I'll just mark a Customer or Site as [InActive] so they can't be used, when appropriate.

If the relationships are considered good form, then what is considered good form when deciding upon which relationships to set to Cascade Update? Do I even need to update autonumber foreign keys?

On upsizing this database, SQL complains that this arrangement is a cyclic update and won't create the relationship unless I tell it to use Triggers. This leads me to question whether I'm correctly using Cascade Updates in my Access db's.

Thank you.

 

[KeithG]

Do you know what this feature does? If the value for a Primary key in a Parent table is changed all corresponding Foreign Keys in the childs tables will be updated.

 

[Len Boorman]

Personally I always set cascade update and cascade delete.

Now some may disagree. Okay no problems there.

When you set cascade update/delete you must then control Who and under what circumstances a PK may be updated or deleted.

Failure to do either is potentially DOOM.

So you pays your money and takes your choice.

Bear in mind allow delete with no cascade and you get referential integrity error

Similarly with update

So you either disallow both or allow both in my book.

Whatever you do you must "Handle the consequences"

L

 

[Shep]

Yes Keith, I know what it does. However, in my limited 15 year run, the only time I've seen an autonumber change is when I empty a table, run a compact and/or repair, close the db and run it again. Accordingly, I've often wondered why set Update on an autonumber field when it's never going to change while there are still records in the table. Seems I read a post some years ago

This uncertainty is most likely due to me having never been formally taught relational db design.

I've stumbled along quite well so far in my Access applications, but am upsizing to SQL and I believe it is less forgiving than Access/Jet. I just don't want to muck the whole thing up by doing something that even a rookie should know better.

It upsizes correctly using Triggers, but I've read from several sources that using the alternative, DRI, is preferred.

Then, there's the cyclic thing.

Len, thanks also. Where I use a Cascade Update I also use a Cascade Delete...I was wondering mostly about a scenario where it is cyclic.

In the relationships in my image, would you set both properties on all three relationships?

 

[Len Boorman]

Shep
I always set cascade update and delete on all relationships. That is the rule that I work to.

No everybody agrees with taht situation, fair enough. However I always know that I must handle all situations where I allow delete of a PK.

If I only set some cascade deletes then I must remember which situations I must handle differently to others. Murpheys Law says I will forget at least one which will result in either a referential error or an undesired cascade delete.

In your diagram if I delete a site why would I want to retain Job details for a site I can no longer identify ?.

It is for you to make the call. This is the manner I apply as a standard to the applications I develop.

Not saying I am right and others wrong but whichever path you take you must consider and handle the consequences.

My method means that I must develop methods whereby a PK cannot be deleted unless it has no child records or I clearly inform the User that cascade delete will occur and the consequences of proceeding further. This I do by showing the consequences and I require then at least 2 confirmations of the desire to continue. Nobody accidently deletes data as a result of a cascade.

L