Relationships and Cascading Updates problem

[Roger]

Subset of Database Tables/Fields are as follows:
Table1 Field1 and Field2 values can occur multiple times in the table
Table2 Field1 has a unique value in the table
Table3 Field1 and Field2 when combined have a unique value

Table1 Field1 and Field2 not indexed
Table2 Field1 indexed - unique values
Table3 Field1 and Field2 multiple field index - unique values

Relationships defined as follows:
Table1 Field1 many-to-one join to Table2 Field1
Table1 Field1 many-to-one join to Table3 Field1
Table1 Field2 many-to-one join to Table3 Field2
Table2 Field1 one-to-many join to Table3 Field1

Objective is to cascade updates through all three tables but this
configuration does not allow Field1 to be updated from any table. Field2
changes cascade correctly from Table3 to Table1.

Any ideas how I can get all fields to cascade updates correctly? TIA

Roger

 

[Michel Walsh]

Hi,


Remove the extra many-to-one relations. Clearly, Table3.Field1 is a
subset of Table2.Field1 (or, but less likely, the reverse), so, pick the
smallest subset as the relation you keep with table1, and make table3.FIeld1
in a one-to-many relation with Table2. With that, any Table1.Field1 value
being in Table3.FIeld1 would also be in Table2.Field1 (by transitivity).


Table1 Table3 Table2
Field1-----> Field1 ----> Field1
Field2-----> Field2 <---- Field2


Note that the design does not inforce the fact that the couple
Table1.(Field1, Field2) be in Table3.(Field1, Field2). Indeed, you can have

Table3
f1 f2
1 a
2 b


and

Table1
f1 f2
1 b
2 a


If that is what you try to modelise, try instead to add a surrogate key:

Surrogates ' table name
pk f1 f2 ' fields

alpha 1 a
beta 2 b


Then,

Table3
surrogate otherFields
alpha ...
beta ...

and

Table1
surropgate otherFields
alpha ...
beta ...



with the relations:


Table1 Surrogates Table3
surrogate --> surrogate <-- surrogate
f1
f2


and then, just cascade the update very easily, from Surrogates... since
there is "nothing" to update, anymore, in other tables ( f1 and f2 don't
appear anymore in table1, neither in table3).



Hoping it may help,
Vanderghast, Access MVP

 

[Roger]

Remove the extra many-to-one relations. Clearly, Table3.Field1 is a
subset of Table2.Field1 (or, but less likely, the reverse), so, pick the
smallest subset as the relation you keep with table1, and make table3.FIeld1
in a one-to-many relation with Table2. With that, any Table1.Field1 value
being in Table3.FIeld1 would also be in Table2.Field1 (by transitivity).

Thanks for the reply Michel.

You are quite right - the problem is the extra many-to-one join. When I
removed the join between Table1.Field1 and Table2.Field1 the cascading
updates appear to work correctly with both Field1 and Field2.
It seems that the Field1 updates are now cascading from Table2 to Table3
then on to Table1.
Apparently works OK now.

Many thanks for your help. Best regards,

Roger