July 12, 2008

The basic principles of relational database design. Part 3

Filed under: Database, MySql — admin @ 1:25 pm

<< Part 2

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

Indicating the Tables Type

The four classification used to identify the table type are data, linking, subset, and validation.

Guidelines for Composing a Table Description

  1. Include a definition statement that accurately identifies the table.
  2. Include a statement that explains why this table is important to the organization.
  3. Compose a description that is clear and succinct. Don’t be too brief or too verbose.
  4. Do not include implementation-specific information in your table description, such as how or where the table is used.
  5. Do not make the table description for one table dependent on the table description of another table.
  6. Do not use examples in a table description.

Interviewing Users and Management

In order to define a good table description for each table, you’ll enlist the help of users and management to establish the table’s definition and importance.

Associating Fields with Each Table

Determine which fields best represent characteristics of the table’s subject and assign them to that table.

Guidelines for Creating a Field Names

  1. Create a unique, descriptive name that is meaningful to the entire organization. There should be only one occurrence of a field name in the entire database. (There is only one exception: when a field is used to establish a relationship between two tables.)
  2. Create a name that accurately, clearly, and unambiguously identifies the characteristic represented by the field.
    Bad Example: “Phone number”; Good Example: “Home Phone”.
  3. Use the minimum number of words necessary to convey the meaning of the characteristic the field represents.
    Bad Example: “Date that employee was hired “; Good Example: “Date Hired”.
  4. Do not use acronyms and abbreviations.
    Bad Example: “Address “; Good Example: “CustAddress”.
  5. Do not use words that could confuse the meaning of the field name.
    Bad Example: “Digital Identification Code Number “; Good Example: “Identification Code”.
  6. Do not use names that implicitly or explicitly identify more than one characteristic.
    Bad Example: “Type & Description “
  7. Use the singular form of the name.

Elements of the Ideal Field

  1. It represents a characteristic of the subject of the table. Subject can be an object or event.
  2. It contains only a single value. Two problems rise with fields that contain more than one value (commonly known as multi-valued fields): data redundancy and difficulty in working with data in the field.
  3. It cannot be deconstructed into smaller components (multi-part field).
  4. It does not contain a calculated or concatenated value.
  5. It is unique within the entire database structure.
  6. It retains all of its characteristics if it appears in more than one table.

Resolving Multi-part Fields

Working with a multipart field is difficult because its value contains two or more distinct items. It’s hard to retrieve information from a multipart field, and it’s hard to sort a group the records in the table by the field’s value.

Resolving Multi-valued Fields

A multi-valued field can potentially store two or more occurrences of the same value. Fortunately, you will recognize a multi-valued field when you see one. The field’s name is often plural and it’s value almost invariably contains a number of commas, which serve to separate the various occurrences that exist within the value itself.

>> Part 4


No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment