Microsoft Access: Defining Relationship Types

Once you have created your tables in Microsoft Access you should have a good idea of how the database will interact, this includes the relationships that will be held between each table. It is crucial to set up table relationships that have appropriate primary and foreign keys and sound naming conventions otherwise tables will contradict one another or hang with inconsistencies (see Referential Integrity). When you do define your table relationships it is important to know that the data type of your primary and foreign keys must be the same and contain the same kind of information. Also bear in mind that if matching number fields then the field size properties must be the same.

Now you may be under the impression that setting up such ‘keys’ is a breach of good database design. That allocating numbers that are held in multiple tables is a duplication of data which we have heavily denounced. However, it is much better practice to duplicate singular fields consisting of singular data items than long sets of data. These ‘keys’ provide validation and resilience to the database structure that far outweighs the amount of space taken in terms of data duplication. What is more, with ‘key’ indexing, data can be queried and manipulated with much greater speed and accuracy.

We have already discussed the use of primary keys within a parent table and foreign keys within a child table. What links these keys is the relationship type, which can be either one-to-one, one-to-many or many-to-many.

These three types of table relationship constitute how data items relate to one another across your tables.

One-to-One

For every record within the parent table there is only one entry that corresponds. Values can be used only once in a one-to-one relationship, which in essence is the equivalent of holding all values in one large table, but instead it is separated in two. As such this relationship type is not very common but can be seen in use within tables that hold too many fields or are too large to handle all of the stored information in one table.

Once-to-Many

For every record within the parent table there must be a corresponding and unique record within the child table. For example one doctor can have many patients, but each patient can have only one doctor. This is the most common type of relationship, where the parent table uses an AutoNumber or Single-Field primary key, or an index set to ‘No Duplicates’.

Many-to-Many

The most complex of relationships, where a record in one table can have many matching records in a second table, and many records in the second table can have many matching records in the first table. It is only possible to have this type of relationship by defining a third (junction) table where the primary key consists of two fields. For example an Order Details table could use the Order ID from the Order table and combine it with the Product ID from the Product table to create a primary key that can be used to establish a many-to-many relationship. In essence a many-to-many relationship is really just two one-to-many relationships with a junction table in between.

1. Close all the tables that you may have open and in the database window choose Tools - Relationships or click on the Relationships button. The Show Table dialog box will be displayed if there are no relationships already created, otherwise you will be presented with all existing relationships and the option to add more tables.

2. Select each table that you want to be related, then click Add. To select multiple tables hold down the Ctrl key and click on each table. Close the Show Table dialog box.

3. Drag the primary key field which appears in bold text from one table to a similar field (the foreign key) in the child table.

Remember, related fields do not have to have the same names but they must have the same data type and hold the same kind of information. Number fields similarly must have the same fieldsize and you can match AutoNumber fields with Number fields if the fieldsize property is set to Long Integer.

4. Click Create in the relationship dialog box. Hopefully you will have successfully established a link and a line denoting the relationship will be displayed. If you drag a field that is not a primary key and does not have a unique index to another field an indeterminate relationship will be created.

5. To save the relationship layout close the Relationships window. Microsoft Access will ask you if you wish to save the layout, click Yes.

6. To view the relationships you have created click on the Show All Relationships button on the toolbar. To view only those relationships defined for a particular table click on the table and then select Show Direct Relationships on the toolbar.

In the following article we will look at Referential Integrity and how setting referential integrity rules for every linked table within your database is an essential part of the database design build process. Without referential integrity your linked tables will be at risk from loss of optimum performance and worst still, data loss.

>>> Microsoft Access: Setting up relationships between tables

>>> Microsoft Access: Understanding Field Properties

>>> Microsoft Access: Understanding Lookup Fields

>>> Microsoft Access: Designing a Relational Database (Part 1)

>>> Microsoft Access: Designing a Relational Database (Part 2)