New Post has been published on https://edztech.org/normalization-of-database-tables/

Normalization of Database Tables

Why are some table structures considered to be bad and others good and how do you recognize the difference between good and bad structures?

From an information management point of view, possibly the most vexing and destructive problems are created through uncontrolled data redundancies. Such redundancies produce update and delete anomalies that create data integrity problems. The loss of data integrity can destroy the usefulness of the data within the database.

Table structures are poor whenever they promote uncontrolled data redundancy. For example, the table structure shown in Figure IM5.1 is poor because it stores redundant data. In this example, the AC_MODEL, AC_RENT_CHG, and AC_SEATS attributes are redundant. (For example, note that the hourly rental charge of $58.50 is stored four times, once for each of the four Cessna C-172 Skyhawk aircraft – check records 1, 2, 4, and 9.)

Figure IM5.1 A Poor Table Structure

          If you use the AIRCRAFT_1 table as shown in Figure IM5.1, a change in hourly rental rates for the Cessna 172 Skyhawk must be made four times; if you forget to change just one of those rates, you have a data integrity problem. How much better it would be to have critical data in only one place! Then, if a change must be made, it need be made only once.

In contrast to the poor AIRCRAFT_1 table structure shown in Figure IM5.1, table structures are good when they preclude the possibility of producing uncontrolled data redundancies. You can produce such a happy circumstance by splitting the AIRCRAFT_1 table shown in Figure IM5.1 into the AIRCRAFT and MODEL tables shown in Figures IM5.2 and IM5.3, respectively. To retain access to all of the data originally stored in the AIRCRAFT_1 table, these two tables can be connected through the AIRCRAFT table’s foreign key, MOD_CODE.

Figure IM5.2 The Revised AIRCRAFT Table

Figure IM5.3 The MODEL Table

Note that – after the revision — a rental rate change need be made in only one place and the number of seats for each model is given in only one place. No more data update and delete anomalies — and no more data integrity problems. The relational diagram in Figure IM5.4 shows how the two tables are related.