Understanding relationships

[Cheese_whiz]

Thanks to the people on this board, I've learned a lot about Access. I find
myself, however, knowing far more how to get things done than I do some of
the bigger picture items.....like relationships.

I'm working on a project that I've based, roughly, on an existing
application. The project (and the existing application) have two main
tables: issues and contacts. In addition to existing parts of the project
from the original application, I've added queries, reports, forms....really
the whole gamut. In doing so, I found myself trying to figurue out the best
way to setup some similar queries (another question I need to get to at some
point in time), and I ended up going to the relationships window in access
for my project.

There were no defined relationships. Now, every issue has four different
people associated with it (five, really, but I haven't added the fifth). All
of these people either esixt in the contacts table, or must be added there
before completing the process of adding an issue.

My questions are:

1. In an existing query, which I've since modified extensively, there are
several tables/queries that are "related" to each other in the design view of
the query. So, I'm confused about the effect of these "relations" in queries
versus the ones you set up in the relationships window. How do these two
types of "relations" ....er.....relate to one another?

2. Since my project functioned, at least with the very limited amount of
test data I've input, fine, does that indicate that the relationships window
and the relations set therein is really all about referential integrity?
What exactly is the benefit of setting up relationships in the relationships
window beyond setting referential integrity up?

3. Are the relations you set up in queries just specific instances such
that you may want to create them in a specific query, but you might now want
to set them up in the relationships window and enforce referential integrity?

I've read several things from various sources about relationships, and I'm
still having trouble getting a real understand of the above. Any responses,
or links to some good articles, would be greatly appreciated.

CW

 

[Allen Browne]

Relationships are the heart and soul of any relational database. The entire
structure is built on many records in one table relating to the primary key
of another table.

Your queries rely on joins between the tables. Naturally, you often join the
tables in the queries the same way as the relationships you created, but you
may join them in other ways, or run queries where they are not joined. Joins
are not the same as relations.

The database designer must ensure the integrity of the data. If your order
table has an order for Customer 123, but your Customer table has no such
customer, your data is nonsense. If you are working with a database that
does not manage this integrity for you (such as the old dBase III from
1980s), you had to write code to handle this in every place where an order
was added or edited, and in every case where a customer was edited or
deleted. When Access 1 was released in 1982, the ability to enforce
integrity via a mouse click in the Relationships window was one of the
reasons for its instant success, as this old page testifies:
http://allenbrowne.com/xbase-07.html

The relationships window not only provides enforced integrity (saving you
heaps of work.) It also provides several ways integrity can be enforced,
including:
a) cascading deletes;
b) cascading updates;
c) cascade to null.
The third one is not well known. More info:
http://allenbrowne.com/ser-64.html

While it is possible to create "relations" without enforced RI, these are
little more than an exercise in drawing lines.

In short:
- JOINs in a query limit the selection of data by matching records in a
table. It is possible to write the same query using criteria (WHERE clause)
instead of a JOIN.

- Relations are the very lifeblood of a relational data, following strict
and clearly defined set-theory rules regarding how entities should be
related to each other. The rules were formulated by Codd and others. There's
heaps of resources on how to build good relations. Here's a list of links to
read further:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

 

[Cheese_whiz]

Thanks a lot for the explanation, Allen.

I think I understood the ref integrity aspects but because I'm new at this
and I've never had to administer/maintain one of these databases, I wasn't
really thinking in terms of that side of it so much as I was just making sure
everything I wanted to accomplish was working. Since queries, and the joins
therein, really do most of the "processing" of the data you collect, my focus
has been more on that side of things.

Ironically, I read a good deal of info on joins yesterday and then never
really made the full distinction between them and the relationships.

I think the answer is more coffee in my diet <G>

Thanks again,
CW

 

[Armen Stein]

Allen makes some great points. Here are a few other things to consider:

Creating relationships in the Relationship window allows you to set up
referential integrity (RI) as discussed earlier. However, specifying
the type of join on a relationship (in other words, the direction of the
arrowheads) has no effect on the tables or RI. It serves only as a
default type when you create a query using those two tables.
Unfortunately, this isn't at all clear when you open the Join Type /
Join Properties from the Relationship window.

Although joins in a query look just like relations in the Relationship
window, they are not the same. Joins in a query govern how the tables
are joined in that query only; they have no effect on RI or anything
else outside of that query.

 

[Tony Toews]

Now, every issue has four different
people associated with it (five, really, but I haven't added the fifth).

I'm wondering if you have five separate fields, one for each of the
five different people, on the issues table? If so what happens if a
sixth person gets involved in the issue?

Or do you have a "junction" table which allows for an unlimited number
of people to be associated with each issue? If so ignore my posting.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Allen Browne]

Okay, that's not an unusual way to learn about Access.

You can build queries that work perfectly, but the results will be
meaningless without RI. Scenario: you build a query that returns the number
of distinct customers who have ordered from you. In 6 months time, the boss
runs the query. It works perfectly, and tells him you have 100 different
customers who placed orders. He's pleased, and asks you to print mailing
labels for a marketing campain. The label run (based on the Customer table)
generates 20 labels. He's not pleased. "Where's the other 80 customers?" You
then discover that someone has been deleting customers who have no current
order. Since there is no integrity between the Customers and Orders table,
your database allows that, so you now have no idea who the missing customers
were.

In short, a database without RI enforced is about as useful as a chocolate
teapot.

 

[loner]

Okay, that's not an unusual way to learn about Access.

You can build queries that work perfectly, but the results will be
meaningless without RI. Scenario: you build a query that returns the number
of distinct customers who have ordered from you. In 6 months time, the boss
runs the query. It works perfectly, and tells him you have 100 different
customers who placed orders. He's pleased, and asks you to print mailing
labels for a marketing campain. The label run (based on the Customer table)
generates 20 labels. He's not pleased. "Where's the other 80 customers?" You
then discover that someone has been deleting customers who have no current
order. Since there is no integrity between the Customers and Orders table,
your database allows that, so you now have no idea who the missing customers
were.

In short, a database without RI enforced is about as useful as a chocolate
teapot.