Home > Articles > Understanding Relational Databases: Referential Integrity

Understanding Relational Databases: Referential Integrity

skip to navigation

This section of the site features articles published between 2002 and 2004. They remain here for reference purposes and may contain information that is out of date.

Chapter Index

This is a free sample chapter from Beginning PHP 4 Databases published by Wrox Press.

Referential Integrity

Many relational database management systems include mechanisms that enforce a database's referential integrity. Referential integrity is another measure of the consistency of the data in a database. Referential integrity is violated when the relation to which a foreign key refers no longer exists.

For example, if one deletes a donor from the Donor table, without also deleting the corresponding donations from the Donation table, then the DonorID field in the Donation record would refer to a non-existent donor. In later chapters, we will discuss a few mechanisms that can be used to enforce referential integrity, including triggers, constraints, transactions, and stored procedures.

Entity Relationship Diagrams

With databases as with programming, manufacturing, and many other disciplines, before we build we should design. The database's design process begins with the design process of the application as a whole, including consideration of user requirements, design patterns, and system requirements. For more information on these topics, see Chapter 10.

An entity relationship diagram is used to illustrate all of the entities that an application must handle, and the relationships among the entities. This information is the blueprint for building the database tables.

Entities typically correspond to nouns that are involved with the process being automated. In the charity example explained in the chapter, nouns include donors, projects, and donations. Relationships correspond to the verbs. Take a look, and make note of how they interrelate. A donor makes donations. A project has multiple donations.

In an entity relationship diagram, entities are shown with their attributes listed below them:

Three columns. Column one titled Donor, with contents +DonorID, +Name and +Country. Column two titled Donation, with contents +DonationID, +DonorID, +Date, +Amount and +ProjectID. Column three titled Project, with contents +ProjectID, +Name and +Description.

There is no one standard way to represent relationships. Some designers use diamond shapes containing a description of the relationship. Others use lines to connect the entities in a relationship. The line should specifically link each foreign key to its designated primary key as shown in the diagram below:

Three columns. Column one titled Donor, with contents +DonorID, +Name and +Country. Column two titled Donation, with contents +DonationID, +DonorID, +Date, +Amount and +ProjectID. Column three titled Project, with contents +ProjectID, +Name and +Description. A line joins column one DonorID to column 2 DonorID. A line joins column 2 ProjectID to column 3 ProjectID.

Types of Relationships

Both of the relationships illustrated opposite are one-to-many relationships. Each record in the Donor table may correspond to many records in the Donation table, however each record in the Donation table has only one corresponding record in the Donor table. Similarly, each project may have multiple donations, but each donation is made to only one project.

In entity relationship diagrams, the "one" side of the relationship is usually represented with a numeral 1, and the "many" side of the relationship with either an M or the infinity symbol:

Three columns. Column one titled Donor, with contents +DonorID, +Name and +Country. Column two titled Donation, with contents +DonationID, +DonorID, +Date, +Amount and +ProjectID. Column three titled Project, with contents +ProjectID, +Name and +Description. A line joins column one DonorID (1) to column 2 DonorID (M). A line joins column 2 ProjectID (M) to column 3 ProjectID (1).

Sometimes relationships are one-to-one. Suppose that each of a manufacturer's products comes with one (and only one) user manual, and each manual only describes the product it accompanies:

Two columns. Column one titled Product, with contents +ProductID, +Name and +Price. Column two titled Manual, with contents +ManualID, +Title, +Author and +ProductID. A line joins column one ProductID (1) to column 2 ProductID (1).

One-to-one relationships are somewhat rare, since one of the entities can often be represented simply as attributes of the other. Some database designers may choose to collapse the schema above into one entity:

One column titled Product, with contents +ProductID, +Name, +Price, +Manual_Title and +Manual_Author.

Such a design decision would depend on several factors, such as the performance or significance of the individual entities within the application. For example, if other entities (like an Author table) in the schema also have relationships with the manual, then it is probably best to keep the Manual entity separate from the Product entity.

Many-to-many relationships are usually represented with the use of mediating one-to-many tables. Returning to our charity example, we can see that there is essentially a many-to-many relationship between donors and projects. Each donor may contribute to many projects, and each project may benefit from multiple donors. If we were to try to include the ProjectIDs in the Donor table, and the DonorIDs in the Project table, we would end up with a denormalized mess on our hands. The introduction of the Donation table deconstructs the many-to-many relationship into two very manageable one-to-many relationships.

A recursive relationship occurs when a column in a table refers to the primary key in the same table. This is very useful when describing hierarchical data structures in a table. In the following table of categories for an encyclopedia web site, the ParentID field refers to the CategoryID field:

CategoryID Name ParentID
1 Home 0
2 History 1
3 Mathematics 1
4 Asian History 2
5 Age of Exploration 2
6 Ancient India 4
7 Trigonometry 3
8 Algebra 3

The History category parents the Asian History category, which in turn parents the Ancient India category, and so on. Each record has a relationship with at least one other record in the same table. The following entity relationship diagram demonstrates the relationship:

One column titled Category, with contents +CategoryID, +Name and +ParentID. A line joins CategoryID (1) to ParentID (M).

> > Summary