Setting Table Relationships- Why?

[el zorro]

When I first started working in Access, I would set
relationships for all the tables.

THen when I got "clever" and began writing lots of
queries, I noticed that I would usually need to tweak the
pre-set relationships depending on the query. For
example, I might change the type of join from what I'd
set in the Relationships view. ANd in some cases, a table
might link to two other tables so I would just not set
the relationship at all until I wrote a query involving
the related tables.

SO... What is the purpose of setting relationships in the
relationships view of the tables? Is it just a
conveinient way of setting the default so the join lines
will show up automatically when working in QUery by
Design?

ALthough I still link the tables, I usually make the
links all one-to-one, then adjust them as required by
individual queries. Should I be doing something
differently?

 

[Douglas J. Steele]

To me, the major reason for relationships is to have the database enforce
referential integrity. I seldom use them for any other reason.

 

[John Vinson]

When I first started working in Access, I would set
relationships for all the tables.

THen when I got "clever" and began writing lots of
queries, I noticed that I would usually need to tweak the
pre-set relationships depending on the query. For
example, I might change the type of join from what I'd
set in the Relationships view. ANd in some cases, a table
might link to two other tables so I would just not set
the relationship at all until I wrote a query involving
the related tables.

Ummm... that's not the case. You can create a Query and set the
relationships there; it is not necessary to define the relationships
in the Relationships window first. And there is nothing to stop you
from creating relationships to two - or any number - of other tables
in the relationships window; it's not necessary to create a Query
first.

SO... What is the purpose of setting relationships in the
relationships view of the tables? Is it just a
conveinient way of setting the default so the join lines
will show up automatically when working in QUery by
Design?

That's just a side effect. The main purpose of relationships is to
enforce relational integrity, so that you cannot (for instance) put a
record in a ClassEnrollment table for a nonexistant student or a
nonexistant class.

ALthough I still link the tables, I usually make the
links all one-to-one, then adjust them as required by
individual queries. Should I be doing something
differently?

One to one relationships are VERY VERY RARE, and usually wrong. If
you're not using "Subclassing", or using one to one relationships to
provide field-level security, you *are* doing something wrong.

If you have a one to one relationship between two tables, with the
exception of the special cases above, you're better off just having
one table with more fields - *if* the tables are properly normalized.

Step back a bit and think about the concepts of "database
normalization". If the term isn't familiar - read about it. Each Table
should represent a particular class of Entities - real-life persons,
things, or events; Entities have real-life Relationships. To continue
the school enrollment example, a Student is a type of entity (with
attributes - fields - such as LastName, FirstName, DateOfBirth, etc.);
a Class is another entity; since each student can enroll in multiple
classes, an Enrollment is another type of entity, with the Student
entity and the Class entity related one-to-many to the Enrollment
entity.

Perhaps you could post a description of the entities important to your
application. My guess is that you SHOULD have relationships, all of
them one-to-many.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[el zorro]

Thanks for your response. I understand what you're saying
about one-to-one relationships. I'm just wondering about
the feature of defining the table relationships in the
table layout view. I'm not enforcing referential
integrity, but I felt that I should probably define SOME
kind of relationship in the Relationships window (not
sure why)so I made them all one-to-one. Then I define the
type of join I actually want when I write the query (such
as one to many). And, depending on what I want to look
at, the join between two tables may change from query to
query. Maybe I should set the joins in the Relationships
view to the type I would normally use-- or does it make
any difference?

 

[John Vinson]

Thanks for your response. I understand what you're saying
about one-to-one relationships. I'm just wondering about
the feature of defining the table relationships in the
table layout view. I'm not enforcing referential
integrity,

Then you're not creating relationships, you're not protecting your
data, and you're misusing Access. All that an unenforced relationship
provides is an overridable default relationship type for new Queries.

If by "defining table relationships in the table layout view" you mean
using the <yuck, PTOOIE!> Lookup Wizard... DON'T. See
http://www.mvps.org/access/lookupfields.htm for a critique of this
misfeature.

but I felt that I should probably define SOME
kind of relationship in the Relationships window (not
sure why)so I made them all one-to-one.

If you're defining Lookups, and also defining relationships in the
relationships window, then Access will create two redundant
relationships, and extra redundant indexes; these will bloat your
database and slow performance. JUST use the relationships window, and
enforce relational integrity - LOGICALLY. If - in real life - there is
a one to many relationship, create a Primary Key in each table, and a
Foreign Key field in the "many" side table, linked to the Primary Key
of the "one" side table. Join the Primary Key to the Foreign Key in
the relationships window, and check Enforce Relational Integrity.
Access will correctly (and automatically) define the relationship as
one to many, since there can only be one record with that Primary Key,
and (unless you've specified a unique index on the foreign key field)
many records in the other table.

Then I define the
type of join I actually want when I write the query (such
as one to many). And, depending on what I want to look
at, the join between two tables may change from query to
query. Maybe I should set the joins in the Relationships
view to the type I would normally use-- or does it make
any difference?

Again... the feature whereby the relationship window sets the default
join type is a handy minor spinoff. That's NOT the reason that
relationships exist; they exist to protect the integrity of your data.
If you don't care about the integrity of your data, that's ok by me...
but don't ask me to use your database, or invest in any business that
relies upon it!


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps