Types of Relationships
Relating One to Many
The Relationship One to Many is the most common relationship there is. In this type of relationship, each Record in the Table, which we will call "Father table", can have more than one Record linked to another Table, which we will call "Son Table". However, every Record from the "Son Table" can only have one Record linked to the "Father table".
For example: in our Data Base, a Client can place several Orders, but each Order can only be done by a single Client. This is an example of a Relationship from One to Many.
Relating Many to Many
In this type of relationships, each Record in the "Father table" can have more than one Record linked to the "Son Table". And each Record in the "Son Table" can have more than one Record linked to the "Father Table".
In our Data Base, for example, we should have a Suppliers Table and another table with their Products. If we want to choose the best offers from our Suppliers, we need to be able to glance at the different offers of those Products. This is an example of a relationship Many to Many, in which we have several Suppliers with the same product and each Supplier can offer several Products.
Relating One to One
In this type of Relationships, each Record in the "Father table" can only have one Record linked to the "Son Table", and each Record in the "Son Table" can only have a maximum of one Record linked to the "Father table".
An example of this type of relationship could be a Table which relates each worker with another Table which holds their CV. In other words, a worker can only have one "Curriculum Vitae" and each CV can only belong to a single worker.
Referential Integrity
Referential Integrity refers to a group of norms or rules which ensure that the data is kept properly related after we've established such relationship. It keeps data from being deleted.
The first of these rules says there can't be Records in a "Son Table" which are not linked to the "Father Table". In our Data Base: All the "Orders" must have been placed by some Client. There can't be "Orders" without "Clients". The Referential Integrity system will be in charge of proving that when we introduce an Order, the Client assigned to it previously exists in the "Clients" table.
The second of these rules states that we can't eliminate (delete) any Record form the "Father Table" if it is related to other Records in the "Son Tables". In other words, Access makes sure that before we delete a Record form the "Clients" Table, there are no "Orders" related to this Client in the "Orders" Table .
