473,574 Members | 13,331 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Purpose of the Relationship Window

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
Nov 12 '05 #1
8 7428
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.Blu e" <an***********@ yahoo.ca> wrote in message
news:9d******** *************** ***@posting.goo gle.com...
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

Nov 12 '05 #2
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.Blu e" <an***********@ yahoo.ca> wrote in message
news:9d******** *************** ***@posting.goo gle.com...
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

Nov 12 '05 #3
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

Nov 12 '05 #4
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***********@y ahoo.ca
(Andante.in.Blu e) 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....]


Nov 12 '05 #5
> 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".
Nov 12 '05 #6
an***********@y ahoo.ca (Andante.in.Blu e) wrote in message news:<9d******* *************** ****@posting.go ogle.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.
Nov 12 '05 #7
> 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
"concatenat es" the list of employees with an extra entry like "none"
with a foreign key of (say) -1?

Thanks,

Alan
Nov 12 '05 #8
an***********@y ahoo.ca (Andante.in.Blu e) 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
Nov 12 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3372
by: google | last post by:
Hi there, I've searched high and low for this, but it seems most people are looking to establish (and control) the relationship between a parent browser window and it's associated child window created with window.open. I would like to break this connection. Why? Because there is an application that I want to open in a new window, but no...
2
3411
by: Larry R Harrison Jr | last post by:
I have an Access 97 database with 2 tables that have a one-many relationship. I have a SQL statement in the "one" table which I want to execute and insert 7 records into the "many" table, and I want them to be linked to the "main" table. Problem is, it won't let me run the SQL run as long as the one-many relationship is established in the...
0
1855
by: leavandor | last post by:
I am trying to design a query that works with a relationship between a Table and a Query. I am comparing a value in the table with a computed value inside the query. The reason for this is that the Query calculates a field to become identical to the corresponding table field, for instance: Table 1 contains field "ID", which is WV008A00...
5
2381
by: BrianDP | last post by:
This is a strange one-- I have an Access 2K database with a split front end/back end. There are two tables in the back end - RFile and RLine. There is a one to many relationship from Rfile.Rnumber to Rline.Rnumber. Originally I went in with the Relationships window, drew the line, and clicked the cascading updates and deletes. ...
7
6762
by: rewalk | last post by:
Hello all! First thank you to everyone in this community that has been helping me over the past few days. It's greatly appreciated and I've learned a lot. At the moment I'm trying to write some SQL code to set up the proper relationship between a created table and an existing table. The existing table is called key metrics and it has an autonumber...
2
1612
by: paii, Ron | last post by:
Is it possible and if so how can I change the background color of the relationship window. I want to make it white.
2
2535
by: paulcjcross | last post by:
How to set up a many to many relationship. I know you need a third table to join the other two. I need one table (table1) with stock_numbers(unique), one table (table2) with job-numbers(unique) and I want to join them with a table (table3) with job-number linked with a stock-number and the number-of-units-of-that-stock. how is it done? so...
4
1844
by: Phil Stanton | last post by:
Having spent ages sorting out the layout of my relationship window with about 60 tables and getting it all tidy and with enough of the tables visible to show the PK an FK relaionshipsOn a subseqent occassion, on opening the relationship window, I found all the tables had shrunk so that in most cases, only the table names were visible and the...
6
1409
by: Bobby Edward | last post by:
I have a fully relational mysql database (pk, fk, etc) If I setup a xsd dataset with 2 related tables do I have to create the relation there too? What's the purpose? Is it just a 'reminder' or does it have an effect on whether the pk/fk constraints are enforced or not at the db level??? Thanks
0
7738
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8081
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8258
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8119
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6482
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5635
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3759
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3771
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
1082
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.