July 15, 2008

The basic principles of relational database design. Part 6

Filed under: Database, MySql, PHP, Zend — admin @ 10:47 am

<< Part 5

Source: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design by Michael J. Hernandez

Elements of a Foreign Key

  1. It has the same name as the Primary key from which it was copied.
  2. It uses a replica of the field specifications for the Primary key from which it was copied.
  3. It draws its value from the Primary key to which it refers.

Establishing a Deletion Rule for Each Relationship

  1. Restrict. The requested record cannot be deleted if there are related records.
  2. Cascade. The requested record will be deleted as well as all related records.

Identifying the Type of Participation for Each Table

  1. Mandatory. There must be at least one record in this table before you can enter any records into the other.
  2. Optional. There is no requirement for any records to exist in this table before you can enter any records into the other table.

Relationship-Level Integrity

  1. Made certain that the connection between two tables in a relationship is sound.
  2. Ensured your ability to insert new records into each table in a meaningful manner.
  3. Ensured your ability to delete an existing record without creating adverse affects.
  4. Established a meaningful limit to the number of records that can be interrelated within the relationship.

What Are Business Rules?

A business Rule is statement that imposes some form of constraint on elements within a field specification for a particular field or on characteristics of a relationship between a specific pair of table.


  • A Ship Date cannot be prior to an Order date for any given order.
  • A student cannot have more than two instruments checked out at the same time.

Types of Business Rules

  1. Database-oriented Business Rules are those that impose constraints that can be established within the logical design of the database.
  2. Application-oriented Business Rules are those that impose constraints that cannot be established by modifying a Field Specification or relationship diagram.

Determining and Establishing Views

There are three categories of Views: date Views, aggregate Views, and validation Views.

  1. Data Views are used to examine and manipulate data from base tables.
  2. Aggregate Views are used to display information that is the result of aggregating a particular set of data in a specific manner.
  3. Validation Views are used to implement data integrity. When a Business Rule limits the range of values of a particular field, you can enforce the constraint just as easily with a validation View as you can with a validation table.

Using a View Specifications Sheet to Record the View

Reviewing and Refining Data Integrity

  1. At the table level:
    There are no duplicated fields in the table.
    There are no calculated fields in the table.
    There are no multi-valued fields in the table.
    There are no multipart fields in the table.
    There are no duplicated records in the table.
    Every record in the table is identified by a Primary key value.
    Each Primary key conforms to the Elements of a Primary key.
  2. At the field level:
    Each field conforms to the Elements of the Ideal Field.
    A set of Field Specifications has been defined for each field.
  3. At the relationship level:
    The table relationship is properly established.
    The appropriate deletion rule has been established.
    The type of participation has been correctly identified.
    The proper degree of participation has been established.
  4. At the level of business rules:
    Each rule imposes a meaningful constraint.
    The proper category has been determined for the rule.
    Each rule is properly defined and established.
    The appropriate Field Specification elements or table relationship characteristics have been properly modified.
    The appropriate validation tables have been established.
    A Business Rule Specification sheet has been completed for each rule.
  5. At the level of views:
    Each View contains the base tables necessary to provide the required information.
    Each View has been assigned the appropriate fields.
    Each calculated field provides pertinent information or enhances the manner in which the data is displayed.
    Each filter returns the appropriate set of records.
    Each View is accompanied by a View Specification sheet.

Assembling the Database Documentation

  1. Final table list.
  2. Relationship diagrams
  3. Field Specifications sheets.
  4. Business Rule Specifications sheets.
  5. Calculated field list.
  6. View diagrams.
  7. Table structure diagrams.
  8. View Specifications sheets.

<< Part 5


No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment