July 13, 2008


The basic principles of relational database design. Part 4

Filed under: Database, MySql — admin @ 11:32 am

<< Part 3

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

Why Keys Are Important

  1. They ensure that each record in a table can be properly identified.
  2. They help establish and enforce various types of integrity.
  3. They are used to establish table relationships.

Establishing Keys for Each Table

There are four maim types of keys: candidate, primary, foreign, non-keys.
A key’s type determines its function within the table.

Elements of a Candidate Key

  1. It must uniquely identify each record in the table.
  2. It must contain unique values.
  3. It cannot be null.
  4. It cannot be a multi-part field.
  5. It comprises a minimum number of fields necessary to define uniqueness.
  6. Its value is not optional in whole or in part.
  7. Its must directly identify the value of each field in the table.
  8. Its value can only be modified in rare or extreme cases.

Artificial Candidate Keys

If none of the fields in a table, either singularly or as a set, qualifies as a Candidate key. “Artificial” is used in the sense that a Candidate key does not “naturally” occur in the table; you have to manufacture one.

Primary Keys
If you have a “simple” (single field) Candidate key and a Composite Candidate key, choose the “simple” Candidate key. It’s always best to use a Candidate key that contains the least number of fields.
Choose the Candidate key that uses a field that incorporates part of the table name within its name. For example, a Candidate key comprising Sales Invoice Number is the good choice for the SALES INVOICES table.

Elements of a Primary Key

  1. It must uniquely identify each record in the table.
  2. It must contain unique values.
  3. It cannot be null.
  4. It cannot be a multipart value.
  5. It should contain the minimum number of fields necessary to define uniqueness.
  6. It is not optional in whole or in part.
  7. It must directly identify the value of each field in the table.
  8. Its value can only be modified in rare or extreme cases.

Rules for Establishing a Primary Key

  1. Each table must have one and only one Primary key.
  2. Each Primary key within the database should be unique no two tables should have the same Primary key unless one of them is a subset table.

<< Part 5

119 Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment