Relational databases must be normalized in order to be effective. Normalization is an important step in designing relational databases. A relational database consists of different tables that talk, or relate, to one another. While denormalization can be used to reduce the number of join tables and may speed up processing, it leads to redundant data and an increase in overall database size.
What is Normalization?
Normalization is how database designers decide on the appropriate fields, tables and table relationships that belong in the database. The benefits of a normalized database are reduced data redundancy and inconsistent dependency, as well as a more intuitive design for users. Redundant data leads to a user having to add, edit or delete the same data from more than one place in the system. Inconsistent dependency forces a user to look in a nonintuitive location for the data.
What is First Normal Form?
Ensuring database normalization is accomplished by using First Normal Form. This eliminates repeating fields in individual tables. An example of a repeating field is Class1, Class2 and Class3. This design will work until a student takes a fourth class. Another part of First Normal Form is to make sure that each field is in the smallest unit possible. For example, instead of having a field for ClientName, the table should have two fields, ClientFirstName and ClientLastName.
What is Second Normal Form?
Eliminating redundant data across tables and records is Second Normal Form. The developer must ensure that each field appears only in one table in order to stop repetitive data entry. The only time that a field can be in a secondary table is when it is used as a foreign key. An example of this is an instructor table that contains the primary key of InstructorID. InstructorID can show up again in a secondary table called Class to identify who is instructing the class.
What is Third Normal Form?
Third Normal Form makes sure that there is not Inconsistent Dependency. Inconsistency Dependency occurs when a field is in an unexpected table. For example, it would not be intuitive to look for a student's phone in the class table. The easiest way to make sure the field belongs in the appropriate table is to ask the question the <field name> of the <table name> is <data>. For example, the <last name> of the <instructor> is <Johnson>. This makes logical sense. An example of Inconsistent Dependency is the <title> of the <instructor > is <Database Design>.
What is Denormalization?
Sometimes it is appropriate for a designer to purposely denormalize parts of a database. A database designer must do this with forethought in order to negate a negative impact to performance and ease of use. By denormalizing the design, less join tables and foreign keys are required. Denormalization can be used on First and Second Normal Form; however, it should never be used on Third Normal Form.
- Photo Credit "database diagram for rollbook system" is Copyrighted by Flickr user: Glutnix (Brett Taylor) under the Creative Commons Attribution license.
Advantages and Disadvantages of Technology in Education
Technology in education has blossomed in the form of course-specific web pages, email communication, online courses, and tools used specifically in the...
The Disadvantages of Risk Management Software
Risk management software plays an important role in businesses that need to manage risk on a daily basis through complex statistical and...