Where should relationships be created?

[lpierce819]

I have been hired as a contract employee to assist in the completion of a
database that was created by a company that does Access training. When the
database was created there were no relationships created between the tables.
My question is this, where is the recommended place to create relationships?

When I initially began using Access I sure thought the recommend place to
create relationships was between tables, has this changed?

 

[Jerry Whittle]

The place to create relationships is in the Relationships window. If you can
enable Referiential Integrity between tables, then the database stands a good
chance of being designed correctly.

You can create relationships between tables and queries in the Relationships
window; however, you can't enforce RI.

You can also do ad hoc relationships between tables and queries in the Query
Design View.

If you split the database, the relationships should be in the Back End
database.

 

[Fred]

Between tables. While most folks focus only on #3, the you could say that
there are three steps in creating relaitonships between records:

1. Design the tables for such. (creating the necessary primary key and
foreigh key fields

2. By some method (hopefully automatic), place the PK values in to the FK
fields of records that are to be linked.

3. "Create the links" in DB structure terms. Via drawing the lines in the
relationships view or by some other methods.


#2 & #3 can be reversed.

 

[John W. Vinson]

I have been hired as a contract employee to assist in the completion of a
database that was created by a company that does Access training. When the
database was created there were no relationships created between the tables.

And they do Access *TRAINING*!!!!? That's appalling! I'd be tempted to "out"
them so future customers could be warned away. Proper normalization (with
referential integrity) should be the focus of the first day's lesson!

My question is this, where is the recommended place to create relationships?

The simplest way is to use the Relationships window, in the database
containing the tables (if it's a split application, the backend).

When I initially began using Access I sure thought the recommend place to
create relationships was between tables, has this changed?

No. You can create relationships between queries but they are just for
documentation, decoration or to make creating new queries a bit easier; they
will not enforce referential integrity or create indexes. The REAL
relationships are between tables.

 

[David W. Fenton]

=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?=

If you split the database, the relationships should be in the Back
End database.

And if you didn't split the database, you should do so BEFORE you
bother with RI, as an unsplit database is going to cause more grief
in the long run than one without RI.