July 14, 2008

The basic principles of relational database design. Part 5

Filed under: Database, MySql — admin @ 12:40 pm

<< Part 4

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

Table level integrity

  1. There are no duplicate records in a table;
  2. Every record in a table is identified by a Primary key value;
  3. Every Primary key value is unique;
  4. Primary key values are not null;

Reviewing the Initial Table Structure

  1. Ensure that the appropriate subjects are represented in the database;
  2. Make certain that the table names and table descriptions are suitable and meaningful to everyone;
  3. Make certain that the field names are suitable and meaningful to everyone;
  4. Verify that all the appropriate fields are assigned to each table;

Why Field Specifications are Important

  1. Field-level integrity is established and enforced as a result of defining Field Specifications.
  2. Defining Field Specifications for each field enhances overall data integrity.
  3. Defining Field Specifications compels you to acquire a complete understanding of the nature and purpose of the data in the database.
  4. Field Specifications are valuable when you implement the database in an RDBMS program.

Anatomy of a Field Specification

  1. General Elements: Field Name, Parent Table, Label, Shared By, Alias(es), Description.
  2. Physical Elements: Data Type, Character Support, Length, Decimal Places, Input Mask, Display Format.
  3. Logical Elements: Type of Key, Uniqueness, required Value, Null Support, Edit Rule, Comparisons Allowed, Values Entered By, Default Value, Range of Values.
  4. Specification Information: Specification type, Based on Existing Specification, Source Specification.

Three Types of Relationships:

  1. One-to-One
  2. One-to-Many
  3. Many-to-Many

Problems with Many-to-Many Relationships

  1. One of the tables involved in the relationship will contain a large amount of redundant data.
  2. Both tables will contain some amount of duplicate data because of the redundancies.
  3. It will be difficult to insert, update, and delete data in the participating tables.

Figure 02. The many-to-many relationship established between the Student and Classes tables.

>> Part 6


No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment