Database Design and Normalization Principles
The Right Database and Normalization
A database is a collection of information formatted into a table, chart, or file. Data tables are generally collections of information inputted into columns, rows and fields.
Columns in each table can be selected through a primary sorting key and there may be unique keys to assist in data retrieval and input.
You may have columns that are fixed in length or vary depending on the type of data that is being input. At the same time, records can also be fixed or varied.
You can restrict column names and keep your column and table names case sensitive.
You can develop a database in any form you desire as long as it is "normal."
There are many way to construct a database which includes the rational database plus the principles of normalization. One example includes database normalization techniques constructed by mathematicians.
These types of data bases are difficult to understand and program unless you have a math background.
To make life easier on those who do not understand all the techniques in mathematical equations, normalized relationship databases can be summarized into these benefits:
- Eliminating redundant data storage. Rather, not allowing the same data to be stored over and over again creating an infinite number of columns. Data can be overwritten, but there will not be multiple entries through normalization.
- Modeling of real world objects or entities and their relation to one another.
- Structuring the data to enable a model to be flexible and adaptable.
- Identify relations between attributes.
- Combining the attributes to the relations of the forms.
- Combine relations or attributes to form a complete database.
Every database has normal attributes and designers need to define the attributes, group each related attribute into relations, select primary and candidate keys for every relation plus remove repeating groups. Functional dependencies must be identified and all transitive dependencies need to be identified.
Is Normalization Normal?
When all the theories have been listed, stated and argued the result is: is normalization normal?
Normalizing your data bases makes sense to the company, does provide great performance, prevents duplication, avoids synchronization problems, and allows programmers to write simpler activities and codes. Using set templates for developing databases provides ease of inputting and use.
Yet what is normal for one department is definitely not normal for another department. Normalization does not fix any problems; it may create more problems.
Measure the data you need and how you will input and retrieve the data contingent on the type of information you are inputting and retrieving. Let your normal be controlled, but also make provisions to customize your data base.
A normalized database is great if you have template data to input and retrieved, but if you have complex data that needs to be retrieved in a specific manner, you need to customize and "denormalize" your database.
"As the old adage goes, normalize until it hurts, denormalize until it works" (Atwood, 2008).
Atwood, Jeff (2008). Coding Horror: Maybe Normalizing Isn't Normal. Available: http://www.codinghorror.com/blog/2008/07/maybe-normalizing-isnt-normal.html. Last accessed: 3 April 2013.
Marston, Tony (2004). Relations Data Model, Normalization and Effective Database Design. Available: http://www.tonymarston.net/php-mysql/database-design.html#what.is.it. Last accessed: 3 April 2013.
Melton, Beth (2009). Databases, Normalizing Access Data. Available: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=88. Last accessed: 3 April 2013.
Senior Consultant at SwiftRadius