Characteristics of Data Relationships - Referential Integrity
A data relationship between two tables allows the user to select existing information instead of typing it. This flow of information brings up issues about what happens if data that exists in a parent table gets deleted while such information has been made available to another table.
When manipulating data that is in a relationship, it is very important to make sure that the records keep their accuracy from one table or source to the other.
To accomplish that goal, some rules must be established to "watch" or monitor the flow of information between two tables. Data or referential integrity is used to check that two tables are related through one (sometimes more than one) field on each table used as the primary key and the foreign key, data entered in the foreign key of a child table must exist in the parent table, otherwise it would be rejected. Only two fields of the same data type are used to establish a relationship between two tables, the tables involved in the relationship belong to the same database.
Cascades on Related Records
After creating a legitimate relationship between two tables, you must make sure that when data changes in the parent table, this change is reflected in the child table. To enforce the rules of data integrity, Microsoft Access provides three check boxes in the Edit Relationship dialog box. First, if you want Microsoft Access to monitor data flow, you can click the Enforce Referential Integrity check box. This would make available two check boxes.
Practical Learning: Integrating Referential Integrity
The Direction of a Relationship
The One-To-Many Relationship
As mentioned already, a relationship between two tables allows one table, the parent, to make its information available to another table (the child). It is likely that the same record on a parent table can be tied to various records in the child table. This type of relationship is referred to as one-to-many because one entry in the parent table can result in many entries in the child table.
To create a one-to-many relationship, in the Edit Relationships dialog box, click the Enforce Referential Integrity check box. The parent table would have a 1 sign on its side of the joining line. The child table would have the infinity symbol on its side of the joining line.
Practical Learning: Managing Referential Integrity
The One-to-One Relationship
A one-to-one relationship is the type of junction between two tables A and B so that one record in table A can have only one corresponding entry in table B and vice versa. Because this is similar to one table of records, this type of relationship is hardly used since you can as well simply create one table.
A Mutual One-To-Many Relationship
Mutual reference is a scenario in which each of two tables references the other. A variant of this feature is where some records of a table A would get their foreign value from a table B but also some records of the table B would get their foreign value from table A. To illustrate this, imagine you have a table of employees and each employee is recorded as belonging to a certain department. Obviously, an employee can (should) belong to only one department. This can be illustrated as follows:
For each department, you may want to specify who the manager is. Obviously, the manager must be an employee, from the table of employees. This can be illustrated as follows:
As another variant to a one-to-many relationship, some records of a table A may get their foreign value from a table B, then some records of table B may get their foreign value from a table C, and finally some records of table C would get their foreign value from table A.
A Many-To-Many Relationship: Junction Tables
A data relationship is referred to as many-to-many if a record from one table A can be represented by many records from another B, and a record from the table B can have a relationship with many records from the first table A. As an example, if you drive your car on a typical day on one road, your road will intersect with many other roads. Of course, someone else driving on other roads would intersect with the road where you are driving. In other words, a road name I-95 would cross (intersect with) other roads such as Rte 1, MD 198, and I-95, etc. On the other hand, Rte 1 also would consider that it crosses (or intersects with) I-95 and MD 32 among others.
To configure a many-to-many relationship in the Relationships window, you may have to add the table twice.
A Junction Table
As a variance of a many-to-many relationship, instead of just two tables, you can create a junction table that unites two or more tables. You create the junction table the same way you do for two tables: Add a foreign key for each of the tables.
A sub-datasheet is a means of displaying the dependent records of a parent record on a datasheet. There must be a table containing a foreign key so that the child table can be, or has been, connected to the parent table.
Creating a Sub-Datasheet
As we have seen so far, to have a relationship between two tables, you must create a primary key in one table and the corresponding foreign key in another table. You can then establish a relationship between both tables in the Relationships window. As stated already, Microsoft Access can take it upon itself to show the related records. You do not have to establish a relationship first in order to take advantage of the sub-datasheet effect. If you have created two tables, one with a primary key and another with a foreign key, you can create the sub-datasheet yourself.
To create a non-existing datasheet, open the table with the primary key in Datasheet View. In the Records section of the Home tab of the Ribbon, click More, position the mouse on Subdatasheet, and click Subdatasheet... This would open the Insert Subdatasheet dialog box. In the list of tables (or queries), you must click the table (or query) that has the foreign key that relates to the primary key of the current table. The names of the primary key and the foreign key would appear in the combo boxes. Once you click OK, Microsoft Access would take care of configuring the subdatasheet.
If a relationship has been established and a subdatasheet exists in a table but you do not want the subdatasheet to show anymore, you can remove it. To delete a subdatasheet, open the table with the primary key in Datasheet View. In the Records section of the Home tab of the Ribbon, click More -> Subdatasheet, and click Remove.
Practical Learning: Creating a Sub-Datasheet