reasons to link tables

[Christopher Glaeser]

I understand the importance if identifying all table relationships during
the database design phase for the purpose of good design, but what is the
purpose of linking tables with the relationship tool? Is there more than
database integrity? For example, do these relationship links affect either
the design view and/or use of queries, forms, and reports?

Best,
Christopher

 

[Douglas J. Steele]

Creating a relationship will add indexes for the foreign keys, if you
haven't already.

 

[Christopher Glaeser]

Creating a relationship will add indexes for the foreign keys, if you

haven't already.

Thanks! I've watched the lynda.com Access tutorials several times regarding
table relationships, and the part I'm still missing is the direction of the
arrow head and the many-to-one vs one-to-many. How does the relationship
builder know the link is one-to-one, many-to-one, or one-to-many? Do the
attributes of the table fields affect this?

Best,
Christopher

 

[John Vinson]

I understand the importance if identifying all table relationships during
the database design phase for the purpose of good design, but what is the
purpose of linking tables with the relationship tool? Is there more than
database integrity? For example, do these relationship links affect either
the design view and/or use of queries, forms, and reports?

The data integrity bit is quite enough reason to link the tables, with
relational integrity enforced; doing so protects you from entering
"orphan" records, misspelled names, duplicate data, etc.

But yes, if you have established a Relationship between two tables,
creating a Query involving those tables will automatically link them
in accord with the relationship. You can change the Join if you need
to, but it's a handy default. In addition, if you create a Form with a
Subform, Access will automatically fill in the linking fields based on
the relationship; likewise for Reports and Subreports.

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

 

[John Vinson]

How does the relationship
builder know the link is one-to-one, many-to-one, or one-to-many?

If the field that is involved in the linkage, in either table, has a
unique Index on it - such as a Primary Key index - that table will be
on the "one" side of the relationship. If you link from Primary Key to
Primary Key you'll get a one-to-one relationship; this is uncommon and
may not be what you want, but there are special cases (Subclassing for
example) where it's legitimate.

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

 

[Albert D. Kallal]

Thanks! I've watched the lynda.com Access tutorials several times
regarding table relationships, and the part I'm still missing is the
direction of the arrow head and the many-to-one vs one-to-many. How does
the relationship builder know the link is one-to-one, many-to-one, or
one-to-many? Do the attributes of the table fields affect this?

Best,
Christopher

When you draw the relationship, after that you need to double click on the
join line..and you are given options. Often, the settings of the fields you
are joining will determine this. For example, if you draw the line from the
Parent (main) table tot he child table,a nd the field in the child table has
its index set to "no duplicates", then the join HAS to be a one to one...as
you can't have more then one child record..can you? If the child table
foreign key (that is just a fancy term for a REGULAR field that is used to
RELATE back tot he main table. Remember, the main table has the primary key,
and child tables use PLAIN REGULAR fields...usually a long number type
field. So, child tables don't use, or even requite a primary key field
(however, you usually have one..since if later on..that table needs to have
other tables related..then you will need a primary key).

So, yes..the attributes of the table fields affect this.

Also, often, you can still draw a line for documentation purposes in the
case of lookup tables. In other words, you might have a person and want to
track favorite colors.

you get:
tblPerson->tblListOfFavorateColors

So, for each new favorite color for one person, you would add a new
record to tblListOfFavorColors:

id tblPerson_ID Color
3 2 Red
4 2 Blue

So, in the above, the first field is a autonumber (key) id ..that we really
don't much need. The 2nd tblPerson_ID is the regular long number field that
points back to the key id (autonubmer) field in tblPerson. The above is a
enforced relationship. However, to make data entry MUCH easier, we might
make the above color field a combo box pop up to a table of colors. So, now
we get:

tblColors
red
blue
etc. etc.

Now, should we draw a line from tblListOfFavoreColros to the tblColors? We
don't have to. I mean, if you delete one record from
tblListOfFavorateColors..there is no special treatment, or anything we have
to do to tblColors. On the other hand, if we delete a record in tblPerson,
then all related records in tblListOfFavoratecoloros must be deleted that
belong to that one person (else we get orphaned records). However, we are
free to add, or even delete records from tblColors..and this table is not
really related to any of the other tables. It is a just nice list to save
typing during data entry. However, even in that case..you might want to draw
a join line..but no sideways 8 (omega) will appear in the relationships
window, since in effect this is just list we use to save typing..but it does
help developers looking at the relationship windows explain what the table
is for. So, you draw the line...but don't enforce RI.

I also enclosing another post I made on how useful the relationships windows
is...the text follows:

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.attcanada.net/~kallal.msn/Articles/PickSql/Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup).

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBookin records, my code also
assumes that a tblBGroup will also have to be added).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.

 

[Sirocco]

Here's an example of a 1-to-many relationship. Let's say you have a list of
customers, and you want each customer to have several addresses. You would
put the customers in 1 table, and their addresses in another. Simply by
defining, let's say, CustomerID, as "indexed/dupes allowed" in the table
with the addresses, you are setting it up to be used as a foreign key on the
many side of a 1-to-many relationship. This table will be on the "many"
side of ANY relationship it's involved in where CustomerID is used as the
related (or foreign) key. When you're in the relationships window drawing
a link between these 2 tables, the actual type of relationship is not really
defined at this point by you, rather, it's understood by Access based on how
you've already defined these indexes when you made (or last edited) the
table, and, significantly, also by the actual contents of the tables. For
example, if 2 records in the addresses table have the same CustomerID,
Access will *assume* a 1-to-many relationship. If you had intended this to
be a 1-to-1 relationship, you have to FIRST delete other records in the
addresses table for that customer, then define CustomerID as "indexed/dupes
not allowed", OR keep the addresses and understand that the relationship is
1-to-many, which might not have been obvious at first (or, as the case may
be).

In a 1-to-1 relationship, there would only be 1 address per customer, in
which case CustomerID in the addresses table would also be primary (i.e,
dupes not allowed).

"Many-to-1" and "1-to-Many" are the same construction. To describe a
relationship as one or the other is a matter of perspective.

 

[Bruce]

Let me approach your question from a conceptual rather than technical
direction. Others here are far more skilled than I at the technical details
anyhow. Ask yourself plain language questions. Is each customer unique?
Can each customer be associated with several orders? Can an order be
associated with several customers? If the answers are Yes, Yes, and No, the
primary key field from the customer table will have a corresponding foreign
key field in the orders table. Drag one field onto the other in the
relationship window and in many cases the details will resolve themselves.
If each order can contain several items, the PK from the orders table has a
corresponding foreign key field in the OrderDetails table. The PK is the One
side of the relationship, and the FK is the Many side of the relationship.
Access will not negotiate this point.

If each employee can attend many meetings, and each meeting can have many
attendees, a third table is needed to resolve the many-to-many relationship.
The third table needs to contain FKs corresponding to the PK from the
Employee table and the PK from the Meetings table.