With the database design, we obtain the best distribution of data in tables and the relationships that must be defined in order to associate data properly.

Tables relate from two table, where one of them will be the main table (origin of the relationship) and the other will be the secondary table (relationships destination).

A relationship is an association that is established between columns (fields) in two tables.

Three types of relationships can be distinguished:

One-to-one relationship: when one record in a table can have only one matching record the another table and vice versa. This type of relationship is used normally when we want to store information that only have some records in a table. For example we have a table of Employees, for each employee we store general data as name, address, ...and for those employees (not many) that have work in foreign countries, we want the total amount of years worked outside, and the total amount of years spend outside. So we can store this information in another table and relate the two tables with an ono-to-ono relationship.

It is not a commonly used relationship type.

One-to-many relationship: When a record in a table can have only one matching record in the other table, and a record from the second table can have many matching records in the first table.

E.g: we have two tables, one with the data of different countries, and another with data of clients. A client live in one country but in one country can live many clients, in this relationship, Clients table is the Related table.

It is the most common type of relationship.

Many-to-many relationships: When a record in a table can have many matching records in a second table and when a record in the second table can have many matching records in the first table. A many-to-many relationship is only possible by defining an intermediate table between the two other tables.

E.g: We have orders and products, one order can have many products and one product can appear on many orders, so we must have a third table Order details to relate products with orders. In this table the primary key consist

• Click on Relationships form Tools menu
  or,
• Click on the Relationships button on the toolbar.
  (The Show table dialogue box will appear:)
• Select required tables and click on the Add button; or double-click the name of the table.
• Finally click on the Close button to finish adding tables.
• Drag the field of the Master table to the equivalent field in the related table.

  Normally you drag the primary key of the primary table.

  To relate tables with two or more fields, first select the fields mantaining CTRL key down, and then drag them.

• The Edit relationships dialogue box appears
• Check on " Enforce Referential integrity"

  If desired, the boxes Cascade Update Related Fields and Cascade Delete Related Records can be activated

• To terminate, click on the Create button.

  The relationship is created and will appear in the Relationships window.

Referential Integrity: If Referential Integrity is checked, Access will not allow us to insert a record in the related table if there is no matching record in the (Master) table.

E.g: We have a Employee table and a Department table, in the Employee table I have a Department field which indicates in which Department the Employeet works in, the two tables should be related by the Department field. In this kind of relation (one-to-many) the Department table is the primary table and the Employee table the related table (A Employee has many Department ). By checking the Enforce Referential Integrity box, we will not be allowed to insert a Employee Department with a Employee table that does not exist in the Department table.

Cascade update the related fields: If checked, when a value is changed in the related field in the primary table, the values in its related records in the related table will be automatically changed.

E.g: If we change the name of a Department in Department table, then automatically in the Employee table all the Employee from this Department will change to the new value.

Cascade delete related records: If checked, when a record is deleted from the primary table, all of the related records in the related table will also be deleted.

E.g: If we delete a Department in the Department table, all the Employee from this Department are automatically deleted from the Employee table.

If options are not selected, Acess does not allow us to change the name of a Department or eliminate a Department if it has any Employee assigned.