Using Relationships window

W

whodat

Is there ever a time when you would NOT use the Access Relationships
design window to establish relationships between tables? The
alternative would be to program relationships into each query design.
In what other circumstances might this be true? Would this be the
case, for example, if the relationships between tables may vary
depending on the query/report?

I realize it is horribly redundant and error-prone to set up
relationships for each query as opposed to doing it once for all, but
I seem to recall a seasoned programmer suggesting it to be the
appropriate approach in some cases.

Thanks,
An "unseasoned" programmer
 
A

Allen Browne

As a rule of thumb, always use relationships with referential integrity
(RI), and set your foreign key fields as required. Anything you can do to
maintain data integrity is worth the effort.

Avoid hotch-potch connections like those you suggested. The only time you
want to do that is with temporary stuff, such as importing existing (bad)
data into a correctly set up database. For the import queries, you can't use
good relationships because the data is already bad.

There are some cases where you cannot use RI, such as between tables that
are in different database files. In these cases, you have lots more work to
do to ensure that the data can never go bad (e.g. blocking the delete or
rename of a primary record while child records exist.)
 
J

Jeff Boyce

I'll echo Allen's comments, and ask what situation you are considering that
would have the relationships between tables changing, based on a particular
query or report?

I'm having trouble imagining a situation in which two tables are related one
way at one time, and another way for another query.
 
T

Tim Ferguson

(e-mail address removed) wrote in 4ax.com:
Is there ever a time when you would NOT use the Access Relationships
design window to establish relationships between tables? The
alternative would be to program relationships into each query design.

(Just to be argumentative...) my answer is for Both -- but not, perhaps,
in quite the way whodat is thinking.

Once again, it comes the new-user-hostile attitude built into Access. The
unfortunate thing is that the Relationships window displays two different
things and calls them both Relationships.

1) RELATIONSHIPS ARE RULES and have an important part to play in
controlling what can be entered into columns, what can be deleted, and
the internal consistency of the database. For example, you can't have
Orders without a valid Customer field, and you can't delete a Customer
record if there are orders attached to it. In access parlance, this
equates to Referential Integrity. The only practical way to create
relationships is in the relationships window[1].

2) Non-RI "relationships" are hints for joins in queries but take no
place in the logical data design. It may be convenient to describe them
in the relationships window but not nearly as convenient as they are
confusing and distracting.

Are there circumstances when you need a join that is not part of a
relationship?

Plenty. Consider the following:

Customers(*CustID, FName, Company, etc)

Orders(*CustID, *OrderNum, InvoiceNum, ShipTo, etc)
FK CustID references Customers

OrderLines(*CustID, *OrderNum, *LineNum, Product, Cost, etc)
FK (CustID, OrderNum) references Orders


Now to find all customers who ordered Foobars -- you need to join
OrderLines to Customers (ON CustID = CustID) although **there is no
Relationship between Customers and OrderLines**.

SELECT FName, Company
FROM Customers RIGHT JOIN OrderLines
ON Customers.CustID = OrderLines.CustID
WHERE OrderLines.Product = 'Foobar'


Therefore, the query designer is for JOINS, and the relationships window
is for RELATIONSHIPS. If only the Access team recognised the
difference... <sigh>


All the best


Tim F
 
B

Bruce Rusk

One situation in which you might need to adjust the relationships in the
query rather than the relationships window: there are cases where you can
have more than one relationship between two tables, on different fields. A
given query might only require that the tables be joined on one of those
relationships and not both, so you would have to delete the relationship in
the query design (if you're using the QBE window) or specify only one
relationship in the SQL.
 
J

Jeff Boyce

Bruce

I'm probably just low on caffeine still this morning, but I'm not
successfully imagining a pair of well-normalized tables that "have more than
one relationship". Can you provide an example?

Thanks!

Jeff Boyce
<MS Office/Access MVP>
 
B

Bruce Rusk

Sure. Here's the longwinded explanation. I offer it in case someone looks at
it and says "that's horrible design, here's how to make it better."

I have a database of books, articles, etc., in which each item is classified
according as one of many "subtypes," (for example, under "Books", there are
"Biography," "Dictionary," "Novel," etc.). These are listed in a table,
tblSubType, with three fields:

SubTypeID ' Autonumber PK
SubTypeName
MajorTypeID

MajorTypeID is a foreign field, from a table called tblMajorTypeID; it can
have values of 1, 2 or 3 (for books, parts of books like chapters, and
articles in periodicals). tblMajorID looks like this:

MajorTypeID ' PK; = 1 2 or 3
TypeName ' Book, Item in Book, Article
DefaultSubType

Each SubType needs to be related to a MajorType (thus there are two entries
in tblSubType for "Short Story": one for a short story in a book, another
for one in a magazine).

But I also needed to have a "DefaultSubtype" for new entries: a new book
will default to the generic "Book" category and the user can specify a more
precise category (such as "novel") later. This DefaultSubType, of course,
must be looked up in tblSubType

In order to ensure that this all works properly, there have to be two
relationships, with referential integrity, between tblMajorType and
tblSubType: one on MajorTypeID in the two tables, and one between SubTypeID
in tblSubType and DefaultSubType in tblMajorType.

Does this make sense?

Bruce
 
T

Tim Ferguson

I'm probably just low on caffeine still this morning, but I'm not
successfully imagining a pair of well-normalized tables that "have
more than one relationship". Can you provide an example?

People(*PersonID, WasBornIn+, CurrentlyLivesIn+)
FK WasBornIn references Cities
FK CurrentlyLivesIn references Cities


HasRelativesLivingIn(*PersonID+, *CityCode+)
FK PersonID references People
FK CityCode references Cities

HasHadAJobIn (*PersonID+, *CityCode+)
FK PersonID references People
FK CityCode references Cities


Cities(*PostCode, FullName, County, etc)


Two one-to-many and two many-to-many relationships between the same two
tables. This might be a bit contrived, but it's not a rare occurrence.

Hope that helps



Tim F
 
J

Jeff Boyce

Thanks, Bruce, Thanks, Tim.

I may still (?again) be caffeine-deprived, but it seems to me you are
describing a situation in which the relationships are known (and I can see
more than one per table pair). I mis-typed.

I was having trouble imagining when a pair of tables would have one (set of)
relationship(s) in one circumstance, and a different (set of)
relationship(s) in another -- that was how I was interpreting the post.

Thanks again (more caffeine!)

Jeff Boyce
<MS Office/Access MVP>
 
T

Tim Ferguson

I was having trouble imagining when a pair of tables would have one
(set of) relationship(s) in one circumstance, and a different (set of)
relationship(s) in another -- that was how I was interpreting the
post.

I hate to sound nit-picking, but this is an area where the language you
use is vitally important.

In Database World, a "relationship" refers to a rule or a constraint, in
other words one that prevents certain updates and deletions that would
break the integrity of the data (i.e. corresponds to "Enforce Relational
Integrity"). Because they are so important to the design, then they are
by definition known in advance -- think of "how many doors are we going
to design into this car?".

As long as you have properly analysed the case, it will nearly always be
true. If you are going to have a field called "WasBornIn", then there is
no point in having it point to a non-existent city: so the rule comes in
to prevent a city being deleted when there are still people who were born
there. That is what a Relationship does. As indicated above, the same two
kinds of things may be related in lots of different ways:

Orders -> IsBilled -> Addresses
Orders -> IsDeliveredTo -> Addresses

and so on.

The thing to remember is that Relationships Have Meanings. This is my
main reason for objecting to having table names like tblDoctorPatient --
it fails to indicate whether the relationship is about SendsBillsTo,
IsCurrentlyCaringFor, MadeTheDiagnosisIn or whatever. Sometimes it is
implicit in the nature of the business (tblOrderProducts) but more often
it isn't. And it leads to fuzzy thinking -- "these two tables should be
linked but I don't know how".

For sure, sometimes it's useful to Join (another technical word in this
context) two tables that are not necessarily Related (although usually
there is an indirect relationship implied). But that is a relationship,
not a Relationship.

If you see what I mean,..


B wishes


Tim F
 
J

Jeff Boyce

Sorry for the delay.

Thanks for the clarification. I'm thinking now that I must have been
considering only Relationships (your description), and not how data in
tables might be used (?relationships?, ?joins?).

Regards

Jeff Boyce
<Office/Access MVP>
 
B

Bruce Rusk

I guess the point is that when you're using the data -- e.g. writing a
query -- you have to *choose* which relationships (or Relationships) are
relevant, otherwise Access will try to use all of them, when a particular
query is usually just dependent on one of them.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top