Relational Database Design Guidelines
When designing a Relational Database, there are many design guidelines
that you should consider. Below you will find detailed guidelines
relating to Primary, Foreign and Candidate Keys and also elements
of the ideal field and table characteristics:
Elements of a Candidate Key
- It can not be a multipart field.
- It must contain unique values.
- It cannot contain null values.
- Its value cannot cause a breach of the organisations security
or privacy rules.
- Its value in not optional in whole or in part.
- It comprises a minimum number of fields necessary to define its
uniqueness.
- Its values must uniquely and exclusively identify each record
in the table.
- Its value must exclusively identify the value of each record within
a given record.
- Its value can be modified only in rare or extreme cases.
Elements of a Foreign Key
- It has the same name as the Primary Key from which it was copied
- It uses a replica of the field specifications for the primary
key from which it was copied
- It draws its values from the primary key to which it refers.
Elements of a Primary Key
- It can not be a multipart field.
- It must contain unique values.
- It cannot contain null values.
- Its value cannot cause a breach of the organisations security
or privacy rules.
- Its value in not optional in whole or in part.
- It comprises a minimum number of fields necessary to define its
uniqueness.
- Its values must uniquely and exclusively identify each record
in the table.
- Its value must exclusively identify the value of each record within
a given record.
- Its value can be modified only in rare or extreme cases.
Rules for Establishing a Primary Key
- Each table must have one, and only one, Primary key
- Each Primary Key within the database must be unique, no two tables
should have the same primary key unless one of them is a subset
table.
Elements of the Ideal Field
- It represents a distinct characteristic of the subject of the
table.
- It contains only a single value.
- It cannot be deconstructed into smaller components.
- It does not contain a calculated or concatenated value.
- It is unique within the entire database structure.
- It retains the majority of its characteristics when it appears
in more than one table.
Elements of the Ideal Table
- It represents a single subject, which can be an object or event.
- It has a Primary Key.
- It does not contain multipart or multivalued fields.
- It does not contain calculated fields.
- It does not containunnecessary duplicate fields.
- It contains only an absolute minimum amount of redundant data.