Purpose of the Relationship Window

[Andante.in.Blue]

Hello, I have just inherited a legacy Access 97 database. While going
through it, I noticed something strange... its Relationships window
(the one accessed by Tools --> Relationships) is almost empty.

Now, as I ponder how a relation database could work without any
relationships, I noticed that the queries of the database defined some
relationships between the source tables and queries. Which leads me
to the question, what is the purpose of the Relationships window? The
database seems to function fine without any of its relationships
defined in that window... all of its relationships are defined in the
relevant queries. Is this "good form"? Would this impact database
efficiency?

I had considered sorting through the database and adding in the proper
relationships myself, but what if I connected a relationship that
contradicted something defined in a query? Would this do more harm
than good? Would the forms and VBA module's SQL statements be broken
by my incessant meddling?

I am at a lost of what to do. The documentation on the database is
virtually non-existent, so I don't know if this madness has method to
it. On the one hand, I want to optimize the database and make it run
smoother. On the other hand, isn't the programmer's oath, "Thou shall
do no harm"? (I just made that up :P )

I would really appreciate any input on this. Thanks in advance for
any comments or suggestions!

Alan

[Terry Kreft]

Relationships are one way of enforcing data integrity.

All they do is ensure that a child record cannot exist without a parent
record; e.g. you can't have invoice lines without having an invoice.

They are not the only way of ensuring this type of integrity.

If the database works and you understand what it is doing then you may well
build trouble for yourself if you start changing the data structure.

Terry

"Andante.in.Blue" <an***********@yahoo.ca> wrote in message
news:9d**************************@posting.google.c om...

Hello, I have just inherited a legacy Access 97 database. While going
through it, I noticed something strange... its Relationships window
(the one accessed by Tools --> Relationships) is almost empty.

Now, as I ponder how a relation database could work without any
relationships, I noticed that the queries of the database defined some
relationships between the source tables and queries. Which leads me
to the question, what is the purpose of the Relationships window? The
database seems to function fine without any of its relationships
defined in that window... all of its relationships are defined in the
relevant queries. Is this "good form"? Would this impact database
efficiency?

I had considered sorting through the database and adding in the proper
relationships myself, but what if I connected a relationship that
contradicted something defined in a query? Would this do more harm
than good? Would the forms and VBA module's SQL statements be broken
by my incessant meddling?

I am at a lost of what to do. The documentation on the database is
virtually non-existent, so I don't know if this madness has method to
it. On the one hand, I want to optimize the database and make it run
smoother. On the other hand, isn't the programmer's oath, "Thou shall
do no harm"? (I just made that up :P )

I would really appreciate any input on this. Thanks in advance for
any comments or suggestions!

Alan

[Fred]

Hi.

Maybe you already tried this,,,

In Access 2000 it is possible to have the relationships but just not be able
to see them until you add the tables to the Relationship window first. I
don't know about Office 97, but here is how it is done in 2000. It might be
the answer...

Open the relationship window.
Click on 'View' in the top of the main access window.
Click on 'Show Table' from that.
Click on 'Tables' tab.
Select what tables you want to see and click on the 'Add' button.
(This adds the tables to the relationship view window)
(Note that it is possible to add view of a table twice!)
Close it.
Try looking at the relationship windows again.

Fred
"Andante.in.Blue" <an***********@yahoo.ca> wrote in message
news:9d**************************@posting.google.c om...

Hello, I have just inherited a legacy Access 97 database. While going
through it, I noticed something strange... its Relationships window
(the one accessed by Tools --> Relationships) is almost empty.

Now, as I ponder how a relation database could work without any
relationships, I noticed that the queries of the database defined some
relationships between the source tables and queries. Which leads me
to the question, what is the purpose of the Relationships window? The
database seems to function fine without any of its relationships
defined in that window... all of its relationships are defined in the
relevant queries. Is this "good form"? Would this impact database
efficiency?

I had considered sorting through the database and adding in the proper
relationships myself, but what if I connected a relationship that
contradicted something defined in a query? Would this do more harm
than good? Would the forms and VBA module's SQL statements be broken
by my incessant meddling?

I am at a lost of what to do. The documentation on the database is
virtually non-existent, so I don't know if this madness has method to
it. On the one hand, I want to optimize the database and make it run
smoother. On the other hand, isn't the programmer's oath, "Thou shall
do no harm"? (I just made that up :P )

I would really appreciate any input on this. Thanks in advance for
any comments or suggestions!

Alan

[Andante.in.Blue]

Thanks Fred, unfortunately, I've already tried showing all the tables,
but nothing shows up. I know what you mean though, I've noticed the
"hidden relations" in Access 2000.

I am just wondering at this point, if there will be any efficiency
benefits if I tried to reconstruct the relationship in this window,
rather than relying on the "on-the-fly" relationships in the queries.
Hence my original question inquiring what exactly the Relationships
Window does.

Terry says that there would be little effect on the database if I
added in the appropriate relationship. Thanks Terry! Intuitively, I
agree with him, but I am open to other opinions.

Alan
"Fred" <fg***************@yahoo.com> wrote in message news:<bj************@ID-76446.news.uni-berlin.de>...

Hi.

Maybe you already tried this,,,

In Access 2000 it is possible to have the relationships but just not be able
to see them until you add the tables to the Relationship window first. I
don't know about Office 97, but here is how it is done in 2000. It might be
the answer...

Open the relationship window.
Click on 'View' in the top of the main access window.
Click on 'Show Table' from that.
Click on 'Tables' tab.
Select what tables you want to see and click on the 'Add' button.
(This adds the tables to the relationship view window)
(Note that it is possible to add view of a table twice!)
Close it.
Try looking at the relationship windows again.

Fred

[Matthew Sullivan]

There was a big thread about this a while back. Many of the big dogs
on this group weighed in with an opinion. The thread is titled
"Explicit Relationships or Not?" and was started 05-27-2002. I'd
recommend looking it up in Google Groups.
The relationship window does a couple of things:

1) As a prior respondent said, it enforces referential integrity.
The emphasis is on "enforce". If you do something wrong in code or a
query, a defined relationship could catch the mistake. I personally
think that's a good thing.

I believe the other person's point was that if you misunderstand the
relationships, and define them incorrectly, you could break a
perfectly functioning database. That is a legitimate concern.

Of course, the flip side to that is that if you do in fact understand
the schema correctly and define the relationships correctly, then
that might help you uncover already existing coding errors.
2) The relationship window also serves as a form of
self-documentation. This is less-often mentioned, and is of course
not the reason it's there, but it does serve that purpose
nonetheless.
-Matt

On 10 Sep 2003 10:59:34 -0700, an***********@yahoo.ca
(Andante.in.Blue) wrote:

[some stuff....]
Hence my original question inquiring what exactly the Relationships
Window does.

Terry says that there would be little effect on the database if I
added in the appropriate relationship. Thanks Terry! Intuitively, I
agree with him, but I am open to other opinions.
[some more stuff....]

[Andante.in.Blue]

> This may be one area to check first. If the cascading updates and

deletes are done with VBA code, then you should see a performance
improvement by changing that.

I cannot find any instances of this where the (assumed) RI is enforce
via VBA code. There aren't many instances where a "cascade delete"
would be used though. A good chunk of the database was designed to
maintain its history -- things that are deleted aren't taken out of
the DB; rather, they are simply marked as "retired" (for employees) or
"completed" (for jobs) and so forth.

If there was an record that needed to be removed from the database, I
think the users would have to manage this manually >< I'm not
exactly happy with that, but the ppl using the DB had adapted (they've
limped along with this thing for 2 years as I have gathered... and
some of the reports weren't even functional!).

The biggest complain about the database (other than the logic flaws,
most of which I have corrected) was the abysmal loading times for some
of the forms. These probably have to deal with many stacked subforms
and controls. I posted another thread trying to figure out what was
wrong, perhaps you could help as well? It is called "Slow Downs
between Form and Subforms".

[Pieter Linden]

an***********@yahoo.ca (Andante.in.Blue) wrote in message news:<9d**************************@posting.google. com>...

Thanks Matthew! I spent most of the morning reading that thread and
it was very educational. I have a few questions though. If I define
a relationship in the window, but I do not check "Enforce RI", would
that accomplish anything?

I have noticed that there are records within several tables that are
not conforming to the (assumed) RI... like having tables that don't
have a defined Primary Key...

Then, there are also some records that can legitimately refer to an
Employee, or be a null value (for instance, a transfer record that
indicates the position is currently is filled by no one at all).
Would this constitute poor database design in the first place? Should
I just "patch this up" with some clever bit of work around?

Alan

If you don't enforce RI, then you can still do stupid things like
create child records without parents. Enforcing RI disallows this.
Not enforcing RI, but setting up the relationship - well, it does do
something. IF you create a form and drop a subform on it and there is
a relationship defined between the two underlying tables, the
relationship is reflected in the form. You can only see the children
of one parent record at a time.

If you screw up RI in your database "patching it up" isn't something
that's always easy to do. It's MUCH better to get it right the first
time, because then the db engine does most of the cleanup work for
you. Except in extreme cases, you shouldn't need workarounds for RI.
If you're dealing with a hierarchy with a rule like "every staff
member at an organization has a boss", you have to remember that
there's one person there that doesn't have a boss - the CEO/the owner.
So it's more important, I think to figure out the business rules and
express them in a way that the DB engine can enforce them for you.

But I'm the village idiot, so never mind.

[Andante.in.Blue]

> If you screw up RI in your database "patching it up" isn't something

that's always easy to do. It's MUCH better to get it right the first
time, because then the db engine does most of the cleanup work for
you.
Yeah, I know what you mean. When I developed my DB, I was very
careful and spent a day drawing up the relationships in it.
Unfortunately, my predecessor here wasn't quite as careful ><
Except in extreme cases, you shouldn't need workarounds for RI.
If you're dealing with a hierarchy with a rule like "every staff
member at an organization has a boss", you have to remember that
there's one person there that doesn't have a boss - the CEO/the owner.
So it's more important, I think to figure out the business rules and
express them in a way that the DB engine can enforce them for you.

What would you recommend in a case like this? A union qeury that
"concatenates" the list of employees with an extra entry like "none"
with a foreign key of (say) -1?

Thanks,

Alan

[David W. Fenton]

an***********@yahoo.ca (Andante.in.Blue) wrote in
<9d**************************@posting.google.com >:

I spent most of the morning reading that thread and
it was very educational. I have a few questions though. If I
define a relationship in the window, but I do not check "Enforce
RI", would that accomplish anything?

The only thing that accomplishes is giving you AutoJoin in the QBE
grid, which you'd already have for cases where the fields have the
same name on both sides of the join.

The only reason I see to establish relatinships without RI is for
documentation purposes. I've done it a few times with *queries* to
document that a query was functioning as a virtual table.

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