PHPBuilder - Database Normalization and Design Techniques



RSS Twitter
Articles Databases

Database Normalization and Design Techniques

by: Barry Wise
|
July 31, 2000

One of the most important factors in dynamic web page development is database definition. If your tables are not set up properly, it can cause you a lot of headaches down the road when you have to perform miraculous SQL calls in your PHP code in order to extract the data you want. By understanding data relationships and the normalization of data, you will be better prepared to begin developing your application in PHP.
Whether you work with mySQL or Oracle, you should know the methods of normalizing the table schema in your relational database system. They can help make your PHP code easier to understand, easier to expand upon, and in some cases, actually speed up your application.
Basically, the Rules of Normalization are enforced by eliminating redundancy and inconsistent dependency in your table designs. I will explain what that means by examining the five progressive steps to normalization you should be aware of in order to create a functional and efficient database. I'll also detail the types of relationships your data structure can utilize.
Let's say we want to create a table of user information, and we want to store each users' Name, Company, Company Address, and some personal bookmarks, or urls. You might start by defining a table structure like this:

Zero Form

users
name company company_address url1 url2
Joe ABC 1 Work Lane abc.com xyz.com
Jill XYZ 1 Job Street abc.com xyz.com
We would say this table is in Zero Form because none of our rules of normalization have been applied yet. Notice the url1 and url2 fields -- what do we do when our application needs to ask for a third url? Do you want to keep adding columns to your table and hard-coding that form input field into your PHP code? Obviously not, you would want to create a functional system that could grow with new development requirements. Let's look at the rules for the First Normal Form, and then apply them to this table.

1
|
2
|
3
|
4
|
5
|
6
Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Barry Wise

Comment:



Comment:

(Maximum characters: 1200). You have characters left.