Search
Web Design Quote
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.
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:
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 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:
