Relationship Types in an Access 2007 Database
There are three types of relationship you can create between tables in an Access 2007 database. Each of these relationship types carries with it specific advantages over the other types. A little planning ahead of time before you begin creating your database can help you determine the types of relationships you are likely to create later, saving you time and effort down the road.
The most common relationship type in Access 2007 is the one-to-many relationship. One-to-many relationships associate one record in one table with many records in another table. For example, suppose you have one table that contains customer information and one table that lists each of the products you sell on your website. Now, suppose you want to track which products each of your customers have ordered. Since it is possible that each individual customer has ordered more than one product, you need a one-to-many relationship between the tables to associate a customer (one) with the products (many) he/she has ordered.
One-to-one relationships are rare in an Access 2007 database because two tables with a one-to-one relationship can be combined into one table. For example, suppose you have a database with customer names in one table and customer addresses in another table. Unless you have a significant number of customers that share the same address, there is no reason to create a one-to-one relationship between the two tables to link each customer with his/her unique address. Simply combine the tables into one table with a “customer" field and an “address" field.
Access 2007 introduced the ability to create a many-to-many relationship between tables. In previous versions of Access, many-to-many relationships were not directly allowed but were possible with the use of a junction table. A junction table was a third table that has a one-to-many relationship with each of the first two tables. The result was an effective many-to-many relationship between the first two tables.
As its name implies, a many-to-many relationship allows you to link many records in one table to many records of another. For example, you may be interested to know what products (many) a customer (one) has ordered in the past. However, you may also be interested in what product (one) has been ordered by each of your customers (many). Creating a many-to-many relationship between a “customers" table and a “products" table would allow you to identify both of these conditions.
Access 2007 still allows you to use junction tables if you prefer. This is especially helpful if you are upgrading a database from a previous version of Access to the 2007 version. However, a new function in Access 2007 allows you to create a many-to-many relationship without a junction table. Multivalue fields can replace the use of junction tables when creating a many-to-many relationship. Multivalue fields allow you to store more than one data item in a field. The result is a many-to-many relationship that can be a powerful tool that reduces redundancy and the size of your database at the same time.