1:1 Relationships: RI Enforcement/Cascading Deletes?

Seems like when there's a 1:1 relationship, the order of referential integrity
enforcement depends on which way you drag the mouse pointer when drawing the
relationship line.

If you drag from tblObject to tblCollection, a tblObject record must exist
before a tblCollection record can be created and if a tblObject record is
deleted, a like-keyed rec in tblCollection will get the cascade delete.

OTOH, if a record in tblCollection is deleted, an orphan is left in
tblCollection.

Personally, I'd avoid 1:1 relationships, but I'm dancing to somebody else's tune
on this project. Somebody decided it was so important that they needed to bring
in a data architecture specialist....wild and crazy guys that they are....trying
to develop to their architecture is, shall we say, "challenging".

On one hand, I keep telling myself that these people have forgotten more than
I'll ever know about "real" database design. On the other hand a little voice
says "JET is different - no triggers....RAD is different - we're in a hurry..."

Am I doing something wrong (besides having a 1:1 relationship)?

Workarounds?
--
PeteCresswell

re: 1:1 Relationships: RI Enforcement/Cascading Deletes?

And your question is...?

- Turtle

"(Pete Cresswell)" <x@y.z> wrote in message
news:c81suvkpc2jne6285dprl4t6k9t5qu6o2i@4ax.com...[color=blue]
> Seems like when there's a 1:1 relationship, the order of referential[/color]
integrity[color=blue]
> enforcement depends on which way you drag the mouse pointer when drawing[/color]
the[color=blue]
> relationship line.
>
> If you drag from tblObject to tblCollection, a tblObject record must exist
> before a tblCollection record can be created and if a tblObject record is
> deleted, a like-keyed rec in tblCollection will get the cascade delete.
>
> OTOH, if a record in tblCollection is deleted, an orphan is left in
> tblCollection.
>
> Personally, I'd avoid 1:1 relationships, but I'm dancing to somebody[/color]
else's tune[color=blue]
> on this project. Somebody decided it was so important that they needed to[/color]
bring[color=blue]
> in a data architecture specialist....wild and crazy guys that they[/color]
are....trying[color=blue]
> to develop to their architecture is, shall we say, "challenging".
>
> On one hand, I keep telling myself that these people have forgotten more[/color]
than[color=blue]
> I'll ever know about "real" database design. On the other hand a little[/color]
voice[color=blue]
> says "JET is different - no triggers....RAD is different - we're in a[/color]
hurry..."[color=blue]
>
> Am I doing something wrong (besides having a 1:1 relationship)?
>
> Workarounds?
> --
> PeteCresswell[/color]

re: 1:1 Relationships: RI Enforcement/Cascading Deletes?

RE/[color=blue]
>And your question is...?[/color]

How to enforce referential integrity and cascading deletes in both directions
when a 1:1 relatinship exists?

i.e. Table1 ===== Table2

Delete a rec from Table1, corresponding record in Table2 is deleted
automatically.

Delete a rec from Table2, corresponding record in Table1 is deleted
automatically.

Seems like either one can happen, but not both.
--
PeteCresswell

re: 1:1 Relationships: RI Enforcement/Cascading Deletes?

You wouldn't really want to enforce referential integrity in both
directions -
unless you never want to add any records.
If a record can't be added to Table2 unless there's a corresponding one in
Table1, and also vice versa, you can't add to either one.

So you choose one table as the Master Table, and enforce RI with deletes
from it to the Child Table.
Now look carefully at the data in the two tables and consider if you really
need to delete from the Master Table if there's no Child data.
For example, suppose the Master Table has the customer name and the
Child Table has the mailing address. (Admittedly, not a particularly
brilliant example.) If you don't have the mailing address, or know it has
changed and you don't have the new one, you might delete that record to
prevent anyone using outdated information, but that doesn't necessarily mean
that you'd need to delete the customer entirely.
If you do come to the conclusion that deleting a Child record really
does require deleting the record in the Master Table, you may want to
provide for that delete with explicit coding. Or you might be able to do it
by basing your form on a query which uses both tables.

HTH
- Turtle

"(Pete Cresswell)" <x@y.z> wrote in message
news:0lhsuv8l3n8hiugs011ktr9t4aavtvp3dc@4ax.com...[color=blue]
> RE/[color=green]
> >And your question is...?[/color]
>
> How to enforce referential integrity and cascading deletes in both[/color]
directions[color=blue]
> when a 1:1 relatinship exists?
>
> i.e. Table1 ===== Table2
>
> Delete a rec from Table1, corresponding record in Table2 is deleted
> automatically.
>
> Delete a rec from Table2, corresponding record in Table1 is deleted
> automatically.
>
> Seems like either one can happen, but not both.
> --
> PeteCresswell[/color]

re: 1:1 Relationships: RI Enforcement/Cascading Deletes?

Yes, Pete. Even 1:1 relationships have a primary table and a related table.
The direction of the cascading delete is from the 1 to the many.

You could drag a 2nd copy of Table2 into the Relationships window, and try
creating another relationship between Table2.ID and Table1.ID where Table2
is the primary table? Never tried, but JET probably won't come at it. If it
did you may well have confused the life out of it, so you better do some
serious testing. Amongst other things, you probably would not be able to add
new records to such a design, since the primary record would be required in
both tables before it could be created in either.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"(Pete Cresswell)" <x@y.z> wrote in message
news:0lhsuv8l3n8hiugs011ktr9t4aavtvp3dc@4ax.com...[color=blue]
> RE/[color=green]
> >And your question is...?[/color]
>
> How to enforce referential integrity and cascading deletes in both[/color]
directions[color=blue]
> when a 1:1 relatinship exists?
>
> i.e. Table1 ===== Table2
>
> Delete a rec from Table1, corresponding record in Table2 is deleted
> automatically.
>
> Delete a rec from Table2, corresponding record in Table1 is deleted
> automatically.
>
> Seems like either one can happen, but not both.
> --
> PeteCresswell[/color]

re: 1:1 Relationships: RI Enforcement/Cascading Deletes?

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:3fee4dc6$0$1721$5a62ac22@freenews.iinet.net.a u...[color=blue]
> Yes, Pete. Even 1:1 relationships have a primary table and a related[/color]
table.[color=blue]
> The direction of the cascading delete is from the 1 to the many.[/color]

*****************
The above statement is very important . . if you really think that a delete
should work both ways then IMHO you data design is screwed . . if your
designers came up with such a structure I would certainly question their
reasoning . . and I was designing DBs in the 70s . . so normalising tables
etc, I do in my sleep . . grin . . . and JET is an excellent engine for
most applications when you know how to really design tables for it . .
usually the better the table design . . the easier the forms/code etc that
come s after it . .

as for 1:1 relationships . . there should be 1 ( ONE ) table that is the
master or primary entity. any other tables related to it are sub-sets of
data and need a very good reason to be seperated from the master. Security
is one good reason. Sensitive data can be stored seprately. Also data that
only occurs in reletively few records, but even this is not a strong reason
.. Or if some data is only rarely needed . and kept seperate for
performance reasons . . .

Ultimately anytime a 1:1 relationship occurs . . consider combining it and
demand a strong reason not to !!!

cheers paul g
ps . . my observations of questions in this newsgroup tell me that far too
many people are focused on clever code and not on underlying and less
glamourous table design . . if they thought out the application spec before
the built their systems they would avoid many of the silly ( read stupidly
unnessasry) problems that get posted here . .

********************
[color=blue]
> You could drag a 2nd copy of Table2 into the Relationships window, and try
> creating another relationship between Table2.ID and Table1.ID where Table2
> is the primary table? Never tried, but JET probably won't come at it. If[/color]
it[color=blue]
> did you may well have confused the life out of it, so you better do some
> serious testing. Amongst other things, you probably would not be able to[/color]
add[color=blue]
> new records to such a design, since the primary record would be required[/color]
in[color=blue]
> both tables before it could be created in either.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "(Pete Cresswell)" <x@y.z> wrote in message
> news:0lhsuv8l3n8hiugs011ktr9t4aavtvp3dc@4ax.com...[color=green]
> > RE/[color=darkred]
> > >And your question is...?[/color]
> >
> > How to enforce referential integrity and cascading deletes in both[/color]
> directions[color=green]
> > when a 1:1 relatinship exists?
> >
> > i.e. Table1 ===== Table2
> >
> > Delete a rec from Table1, corresponding record in Table2 is deleted
> > automatically.
> >
> > Delete a rec from Table2, corresponding record in Table1 is deleted
> > automatically.
> >
> > Seems like either one can happen, but not both.
> > --
> > PeteCresswell[/color]
>
>[/color]

re: 1:1 Relationships: RI Enforcement/Cascading Deletes?

RE/[color=blue]
>The above statement is very important . . if you really think that a delete
>should work both ways then IMHO you data design is screwed . . if your
>designers came up with such a structure I would certainly question their
>reasoning . .[/color]

I think this is going to be a blessing in disguise: a concrete technical reason
to depart from these guys' architecture. No bruised egos, just a different
requirement because the design isn't for the "real" databases they're used to.

As far as I can see, the departure wouldn't be any change in the logical
archicture - only physical.

What they're doing is using an "Object" table as a middleman for two other
tables - which lets them make some link tables do double duty.

e.g.
Element-->Object-->ObjectComment-->Comment
Collection-->Object-->ObjectComment-->Comment

All I'd do is add a second set of link tables.

viz:
Element-->ElementComment-->Comment
Collection-->CollectionComment-->Comment


I can't see any function that "Object" is peforming except to make the diagram
have less tables on it - i.e. look prettier. OTOH, it requires extra code and
has to use more MIPs when retrieving data.
--
PeteCresswell

re: 1:1 Relationships: RI Enforcement/Cascading Deletes?

x@y.z ((Pete Cresswell)) wrote in
<0lhsuv8l3n8hiugs011ktr9t4aavtvp3dc@4ax.com>:
[color=blue]
>RE/[color=green]
>>And your question is...?[/color]
>
>How to enforce referential integrity and cascading deletes in both
>directions when a 1:1 relatinship exists?
>
>i.e. Table1 ===== Table2
>
>Delete a rec from Table1, corresponding record in Table2 is
>deleted automatically.
>
>Delete a rec from Table2, corresponding record in Table1 is
>deleted automatically.
>
>Seems like either one can happen, but not both.[/color]

There is no logical relation that would work the way you describe,
so far as I can imagine.

I can't think of a use for 1:1 relationships that is not some form
of main type/sub type relationship, so there is, by definition, one
main table, with the parent record, and child records, even though
they are 1:1.

A 1:1 where both tables are "parents" makes no sense to me at all,
and sounds to me like two independent entities, or duplication that
belongs in a single table in the first place.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: 1:1 Relationships: RI Enforcement/Cascading Deletes?

macdermott@nospam.com (MacDermott) wrote in
<uVrHb.3399$d4.1176@newsread1.news.atl.earthlink.n et>:
[color=blue]
>You wouldn't really want to enforce referential integrity in both
>directions -
> unless you never want to add any records.[/color]

Well, it all depends on what you use for your join.

If each table has its own AutoNumber PK, and a foreign key pointing
to the other table, that foreign key can be non-required, so you
could add records with blank foreign key. Yet, once populated, you
could have a cascade delete relationship. Since you're dealing with
two different foreign keys, seems you could define it the way Pete
described (to my surprise!), but I still can't see *why* you'd do
this!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: 1:1 Relationships: RI Enforcement/Cascading Deletes?

RE/[color=blue]
>A 1:1 where both tables are "parents" makes no sense to me at all,
>and sounds to me like two independent entities, or duplication that
>belongs in a single table in the first place.[/color]

To use technical terminology: I think it's wacked too.

Snip FromPrevious post:
----------------------
What they're doing is using an "Object" table as a middleman for two other
tables - which lets them make some link tables do double duty.

e.g.
Element-->Object-->ObjectComment-->Comment
Collection-->Object-->ObjectComment-->Comment

All I'd do is add a second set of link tables.

viz:
Element-->ElementComment-->Comment
Collection-->CollectionComment-->Comment

I can't see any function that "Object" is peforming except to make the diagram
have less tables on it - i.e. look prettier. OTOH, it requires extra code and
has to use more MIPs when retrieving data.
-------------------------
--
PeteCresswell