What are the types of relationship that can be applied in Access database?

In the sample database that came with this book, relationships have already been established between the tables. Take a look at some of these relationships to get a better idea of how they can be set and changed. Go to the application ribbon and select the Database Tools tab. Then select Relationships to view the existing relationships for this database. As shown in Figure below, the tables are represented with lines between them signifying the relationships.

Relationships define a database. The power of a database lies in the way the tables are related to each other. Nearly every database that contains multiple tables also includes relationships between these different sets of information. There are three types of table relationships: one-to-many, many-to-many, and one-to-one. Let's learn about each type.

For Microsoft Access classes in Los Angeles call us on 888.815.0604.

One-to-Many

Consider the following database, which contains two tables:

This database has a table of Employees and a table of Expenses. As time goes by, the Expenses table could grow to become quite large.

Now imagine that you want to find out the phone number of every employee that submitted an expense. It is impractical to place the employee phone number in the same table as the expenses because it will create a lot of unnecessary and duplicate data. This is particularly true if there is another table containing employee data elsewhere.

Therefore, a relationship between the two tables can be created between the two common fields: Employee ID. The Employees table and the Expenses table are then in a one-to-many relationship, meaning that one entry in the Employees table can relate to many entries in the Expenses table. In other words, one employee can log many expenses.

Many-to-Many

Consider a database that contains two separate tables: Orders and Products. While a single order can include more than one product, a single product can appear on many orders. Therefore, for each record in the Orders table, there can be many records in the Products table. In addition, for each record in the Products table, there can be many records in the Orders table. This type of relationship is called a many-to-many relationship because, for any product, there can be many orders and, for any order, there can be many products.

In order to represent a many-to-many relationship, a third table will need to be created. Such tables are often called junction tables, and they break down the many-to-many relationship into two separate one-to-many relationships. In our example, the Orders table and the Products table have a many-to-many relationship that is defined by creating two one-to-many relationships to the Order Details table. One order can have many products, and each product can appear on many orders.

One-to-One

A one-to-one relationship requires each record in one table to have only one matching record in a second. Due to these constraints, one-to-one relationships are relatively rare, as information that is related in this way is most often stored in the same table. Most often, such relationships are used to divide and isolate parts of a table for security purposes.

Summary

When designing a database, relationships are frequently the most challenging step and often the place where most of the confusion with databases arises. However, as with most things, proper planning can make a world of difference.

Microsoft Access can create either type of database — flat file or relational. However, most Access databases tend to be of the relational type. Below is an example of a relational database that tracks customers and their orders in two separate—but related—tables.

Now let’s look at the different types of table relationships. When you link tables together, they form one of three possible relationships.

In a relational database (Access), the data in one table is related to the data in other tables. In general, tables can be related in one of three different ways: one-to-one, one-to-many or many-to-many.  The relationship is used to cross reference information between tables.

One to One

In a one-to-one relationship each record in one table has at most one related record in another table.

In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table.

One to Many

A one-to-many relationship, often referred to as a "master-detail" or "parent-child" relationship.

A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.

Many to Many

In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key consists of two fields Ã¯Â¿Â� the foreign keys from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table.

A many-to-many relationship means that for each record in one table there can be many records in another table and for each record in the second table there can be many in the first.

Many-to-many relationships can not be directly represented in relational database programs and have to be built by using two or more one-to-many relationships.

Defining relationships

You define a relationship by adding the tables that you want to relate to the Relationships window, and then dragging the key field from one table and dropping it on the key field in the other table.

The kind of relationship that Microsoft Access creates depends on how the related fields are defined:

• A one-to-many relationship is created if only one of the related fields is a primary key or has a
• A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes.
• A many-to-many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields ï¿Â� the foreign keys from the two other tables.

Note If you drag a field that isn't a primary key and doesn't have a unique index to another field that isn't a primary key and doesn't have a unique index, an indeterminate relationship is created. In queries containing tables with an indeterminate relationship, Microsoft Access displays a default join line between the tables, but referential integrity won't be enforced, and there's no guarantee that records are unique in either table.

When creating a relationship between two tables MS Access provides uses the Referential Integrity feature. This feature prevents adding records to a detail table for which there is no matching record in the master table. It will also cause the key fields in the detail table to be changed when the corresponding key fields in the master are changed - this is commonly referred to as a cascading update. The second option is to enable cascading deletes. This causes the deletion of all the related records in a detail table when the corresponding record in the master table gets deleted.

Which of the following type of relationship can be applied in Access database?

What are relationships in Access database?

What are the four 4 types of relationships in a database?

What are the 3 types of relationships in a relational database?