Once you’ve decided upon and drafted the tables and fields you believe that you will require in your database, the next stage is to enter some sample data in the tables and look for any potential problems with maintaining the data.
Identifying possible data maintenance problems before you enter your real data and start working with your database will save you many headaches and extra work later on. Improperly designed tables can also result in inaccurate data, and you certainly don’t want to be making decisions based on data that you can’t trust.
Data normalization ensures that, when you put into practice the tables you have planned, the resultant database will be able to efficiently provide the database queries and reports desired and ongoing data maintenance will be as straightforward as possible.
Common data normalization procedures are as follows:
In the following example the chosen fields are not correctly normalized. The Name field is not the smallest meaningful value. There are two email address fields, and the second will not always have a value.
In this example, each field has the smallest meaningful value, there are no repeated groups of fields or repeated data values, and every record in the table will have a value in each of these fields.
Though not a strict rule, in many cases blank values in fields should appear because that value is temporarily missing or unknown—not because that field does not pertain to a record.
So, for example, if you have a table listing Company details that contains a web site field, that field may possibly contain a blank value for some Companies (assuming that not all of them may have a Web site). The value isn’t temporarily missing or unknown; the field just doesn’t apply to every record. That’s a sign that the field might belong in a separate table.
Denormalization occurs when a database designer combines data into one table that the normalization process indicated should be in two tables. After a database designer has fully normalized the design of a database, he or she may, on occasion, choose to do this for any number of reasons:
A common example of denormalization is allowing an occasional field to be blank for some records. For instance, a database may contain a middle initial field, but not all employees have or use a middle initial. Strict normalization rules would state that the field should not be null—but it is common practice to keep this field in the same table with the first and last names.