you try out your initial database, you will probably discover room for
improvement. Here are a few things to check for:
Did you forget any columns? If so,
does the information belong in the existing tables? If it is information
about something else, you may need to create another table. Create a
column for every information item you need to track. If the information
can’t be calculated from other columns, it is likely that you will need a
new column for it.
Are any columns unnecessary
because they can be calculated from existing fields? If an information
item can be calculated from other existing columns — a discounted
price calculated from the retail price, for example — it is usually
better to do just that, and avoid creating new column.
Are you repeatedly entering
duplicate information in one of your tables? If so, you probably need to
divide the table into two tables that have a one-to-many relationship.
Do you have tables with many
fields, a limited number of records, and many empty fields in individual
records? If so, think about redesigning the table so it has fewer fields
and more records.
Has each information item been
broken into its smallest useful parts? If you need to report, sort,
search, or calculate on an item of information, put that item in its own
Does each column contain a fact
about the table’s subject? If a column does not contain information about
the table’s subject, it belongs in a different table.
Are all relationships between
tables represented, either by common fields or by a third table?
One-to-one and one-to- many relationships require common columns.
Many-to-many relationships require a third table.
Refining the Products table
that each product in the product sales database falls under a general category,
such as beverages, condiments, or seafood. The Products table could include a
field that shows the category of each product.
that after examining and refining the design of the database, you decide to
store a description of the category along with its name. If you add a Category
Description field to the Products table, you have to repeat each category
description for each product that falls under the category — this is not a
better solution is to make Categories a new subject for the database to track,
with its own table and its own primary key. You can then add the primary key
from the Categories table to the Products table as a foreign key.
Categories and Products tables have a one-to-many relationship: a category can
include more than one product, but a product can belong to only one category.
you review your table structures, be on the lookout for repeating groups. For
example, consider a table containing the following columns:
each product is a repeating group of columns that differs from the others only
by adding a number to the end of the column name. When you see columns numbered
this way, you should revisit your design.
a design has several flaws. For starters, it forces you to place an upper limit
on the number of products. As soon as you exceed that limit, you must add a new
group of columns to the table structure, which is a major administrative task.
problem is that those suppliers that have fewer than the maximum number of
products will waste some space, since the additional columns will be blank. The
most serious flaw with such a design is that it makes many tasks difficult to
perform, such as sorting or indexing the table by product ID or name.
you see repeating groups review the design closely with an eye on splitting the
table in two. In the above example it is better to use two tables, one for
suppliers and one for products, linked by supplier ID.
Applying the normalization rules
can apply the data normalization rules (sometimes just called normalization
rules) as the next step in your design. You use these rules to see if your
tables are structured correctly. The process of applying the rules to your
database design is called normalizing the database, or just normalization.
is most useful after you have represented all of the information items and have
arrived at a preliminary design. The idea is to help you ensure that you have
divided your information items into the appropriate tables. What normalization
cannot do is ensure that you have all the correct data items to begin with.
apply the rules in succession, at each step ensuring that your design arrives
at one of what is known as the “normal forms.” Five normal forms are
widely accepted — the first normal form through the fifth normal form.
This article expands on the first three, because they are all that is required
for the majority of database designs.
First normal form
normal form states that at every row and column intersection in the table
there, exists a single value, and never a list of values. For example, you
cannot have a field named Price in which you place more than one Price. If you
think of each intersection of rows and columns as a cell, each cell can hold
only one value.
Second normal form
normal form requires that each non-key column be fully dependent on the entire
primary key, not on just part of the key. This rule applies when you have a
primary key that consists of more than one column. For example, suppose you
have a table containing the following columns, where Order ID and Product ID
form the primary key:
Order ID (primary key)
Product ID (primary key)