Relationship Concern

[FerryMary]

Jeez, that subject sounds like a lonely hearts club subject line.

Anyway,,
I've gotten so far thanks to you good people and I now have a question
regarding a suggestion from the performance tool. In a couple of cases it
suggests that I relate a table to itself. I can just select it and hit
optimize, but was curious why it would do this. The only thing I've
discovered is that in both instances I'm using a combo box in the form
header(of tables in question) to locate records within the table. The
suggestion looks something like:

"Relate 'tblEquipSvc' to 'tblEquipSvc'."

Everything is working very well, I just wondered about this. Thanks
Everybody.

Mary

 

[Tim Ferguson]

I now have a question
regarding a suggestion from the performance tool. In a couple of
cases it suggests that I relate a table to itself.

Need more information. I have never used a performance tool so I can't
really guess what it's trying to do to your data, but I know that you
need to understand before allowing it to do anything.

Joining a table to itself is a recognised structure, called a Self-Join.
This usually features in heirarchical sorts of structures, like employees
in an organisation:

Name Status WorksFor
==== ------ --------
Eric Secretary John
Fred Supervisor John
Gavin Underling Fred
Hugh Boss <null>
Iain Manager Hugh
John Dept Head Iain

and so on. There are complicated reasons, which I don't understand, why
this is not always a good solution, but it's at least understandable.
Other self joins are used in Bills of Materials, Family Trees and so on.

You can also have indirect self-joins, where a table is related to second
table that is related back to the first one again. For example:

Name
====
Alex
Betty
Charlie
Davina
Eric
Fred

One Verb Two
=== ---- ===
Alex IsHusband Betty
Betty IsWife Alex
Betty IsMother Charlie
Alex IsFather Charlie
Charlie IsSon Alex
Charlie IsSon Betty
Betty Hates Davina
Davina Loves Alex

and so on. Does this correspond to your situation at all?

Best wishes


Tim F

 

[FerryMary]

Thank you,

your response actually was a perfect example of why my tables involved
probably did need the 'self-join'. (thanks again, I like knowing what to call
something) The tables involved were indeed parent tables where in cases
related back to themselves. tblemployees does relate back to itself as
employee supervisor is looked up in a tblfacilitysupervisors which locates
the employee from tblemployee. It was a similar situation in all three
circumstances.

thanks again
Mary