Understanding Relational Databases: Referential Integrity
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.
This is a free sample chapter from Beginning PHP 4 Databases published by Wrox Press.
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:
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:
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:
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:
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:
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
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
|5||Age of Exploration||2|
History category parents the
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: