Build Relational Databases in Seven Steps
By Manas Mawroof
The following is an ordered list of steps you can follow to create a good database design
for your web application.
Step 1- Create a charter for the database
First keep in your mind why you need to create a database and what you are expecting
it to do. Database designers sometimes fall into trouble when.
1. They assume that the data exists for its own sake
2. They assume that the database exists for the sake of the Information Technology department.
Both the above are wrong, and the database exists for its users. Before you design your
database, find out why the users of your application need the database and what the users expect
to accomplish from it. You can think of this purpose statement as a mission statement or a
charter for the database.
The more complicated and complex your application is, the more detailed your Charter
will be. But always limit your charter to the key points that identify the unique capabilities
that the user expects from the database.
The most vital part of this step is to examine how the data is currently being stored
and to uncover the weaknesses. If the data is currently being stored in an Excel spreadsheet
or is stored in paper form in file cabinets, carefully examine them to see what kinds of data
are included in them.
Step 2 - Make a list and double check it
Once you finish Step 1, and you are done with your Charter, start listing the major tables
of the database you are going to create.
When you have come up with a rough list of tables and the data items, always keep in mind
them as entities that are like real-world objects that the database needs to keep track of.
Step 3 - Add Keys
For every database you create, there should be a column or combination of columns that
uniquely identifies each row in a table. This column or combination of columns is also known
as the Primary Key of the table.
In this step you will have to figure out the primary and foreign keys for each table
design and add appropriate ID fields to overcome the problem stated above.
Step 4 - Normalize the Database
The next step is the Normalization process. Normalization refers to the process of eliminating
redundant information and other problems in the database design. In order to normalize the
current database design, you need to identify the problems in the design and correct them,
often by creating additional tables.
Five different levels of normalization exist, known as the five normal forms.
1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Fourth Normal Form (4NF)
5. Fifth Normal Form (5NF)
The following are the corrections that need to be handled:
1. Maintain Consistency - use same field names for fields used as primary keys and foreign
keys, and change all tables to plural names.
2. Data Redundancy
First Normal Form - 1NF
A table is in the 1NF when each table is free of repeating data.
Second Normal Form - 2NF
The 2NF only applies to composite a key table, that is if the table contains a primary
key made up of two or more table columns. In order to be in the 2NF, every column in the table
must depend on the entire primary key, not only a part of it.
Third Normal Form - 3NF
A table is in the 3NF form when the table is in 2NF and every column in the table depends
on the entire primary key and none of the other fields depend on each other.
Step 5 - Denormalize the Database
When you come across a situation where you ben the normalization rules a bit, the database
will operate more efficiently. For instance having data redundancy in the database can increase
the performance of your database. This process of adding data redundancy to increase the efficiency
and performance of your database is called denormalization.
| 
About BTH
User Agreement
Privacy
Site Map
Contact
News Feeds
RSS Feed
Follow @Stephen Bucaro
Active Server Pages
