Basic concepts on relationships.


Relational databases.

 

In a relational database we have data organized in tables and programs (the DataBase Management System) that manage the data. In Access 2003, The Microsoft Jet is the database engine that manage the data.

A relational database allows the simultaneous use of data from more than one table and the control on data.

By using relationships, one can avoid the duplicity of data, saving memory and space on the drive, speeding up the execution and use of data in tables.

To achieve a correct and eficious relational database it is essential to carry out previous studies about databases design.

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 to two, where one of them will be the main table (origin of the relationship) and the other will be the secondary table (relationships destination).

Types of relationships.

 

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

 



   
   
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.