Database Normalisation: Essential to Successful Database Design and Maintenance
People use the terms data and information interchangeably in day-to-day speech, but they mean different things. Data refers to actual facts such as a date, a number or a name. Once data is processed, organised and associated with other data, then information is created. For example, the raw data point might be your name, but added to other data points such as your date of hire, and your organization ID number, creates information. Data like these are stored in databases, which are carefully designed and organised to make information retrieval fast and accurate. The table, a two-dimensional structure composed of columns and rows, is the primary tool used to organise data.
Database designers and administrators use the process known as database normalisation to help organise the database into tables with the goal that tables should have a specific purpose. Limiting tables to a single purpose reduces data duplication and improves the accuracy, performance and efficiency of the database. There are three main benefits to normalizing a database:
Reduce data redundancy
Prevent issues when data needs to be modified
In practical terms, the way that data is organised for storage is different from the information that end-users require. An example would be that a manager might want to see the details of a sale that include the customer name, billing address, contact number, as well as order placement and fulfillment details. These pieces of data might be stored in different tables that need to be associated together for one particular sale.
Before starting the normalisation process, database administrators prepare by understanding and identifying the business rules behind policies and procedures. This requirements analysis helps the database designer get consensus behind the definition and uses of data items. After this, the relationship between the data needs to be established and these form the basis of the table design.
Databases that are poorly normalised cause a number of problems ranging from excessive disk usage, poor system performance and inaccurate data. Non-normalised databases also exhibit insertion, update and deletion anomalies which make maintaining the database more difficult.
During database normalisation, database administrators have four objectives. The first is to arrange data into logical groups that each describes a specific portion of the whole. Next is to minimize data redundancy. The third is to organise the data so that modifications only need to be done in one place. The final goal is to build the database so that data changes can be done quickly and efficiently while at the same time maintaining the integrity of the stored data.