databasedev.co.uk - database solutions and downloads for microsoft access

Relational Database Design

Data Redundancy Defined

Data redundancy is a data organization issue that allows the unnecessary duplication of data within your Microsoft Access database. A change or modification, to redundant data, requires that you make changes to multiple fields of a database. While this is the expected behaviour for flat file database designs and spreadsheets, it defeats the purpose of relational database designs. The data relationships, inherent in a relational database, should allow you to maintain a single data field, at one location, and make the database’s relational model responsible to port any changes, to that data field, across the database. Redundant data wastes valuable space and creates troubling database maintenance problems.

To eliminate redundant data from your Microsoft Access database, you must take special care to organize the data in your data tables. Normalization is a method of organizing your data to prevent redundancy. Normalization involves establishing and maintaining the integrity of your data tables as well as eliminating inconsistent data dependencies.

Establishing and maintaining integrity requires that you follow the Access prescribed rules to maintain parent-child, table relationships. Eliminating inconsistent, data dependencies involves ensuring that data is housed in the appropriate Access database table. An appropriate table is a table in which the data has some relation to or dependence on the table.

Normalization requires that you adhere to rules, established by the database community, to ensure that data is organized efficiently. These rules are called normal form rules. Normalization may require that you include additional data tables in your Access database. Normal form rules number from one to three, for most applications. The rules are cumulative such that the rules of the 2nd normal form are inclusive of the rules in the 1st normal form. The rules of the 3rd normal form are inclusive of the rules in the 1st and 2nd normal forms, etc.

The rules are defined as follows:

1st normal form: Avoid storing similar data in multiple table fields.

  • Eliminate repeating groups in individual tables.
  • Create a separate table for each set of related data.
  • Identify each set of related data with a primary key.

2nd normal form: Records should be dependent, only, upon a table’s primary key(s)

  • Create separate tables for sets of values that apply to multiple records.
  • Relate these tables with a foreign key.

3rd normal form: Record fields should be part of the record’s key

  • Eliminate fields that do not depend on the key.

The 3rd normal form suggests that fields, that apply to more than one record, should be placed in a separate table. However, this may not be practical solution, particularly for small databases. The inclusion of additional tables may degrade database performance by opening more files than memory space allows. To overcome this limitation, of the third normal form, you may want to apply the third normal form only to data that is expected to change frequently.

Two, more advanced, normal forms have been established with application that is more complex. The Failure to conform to the established rules of these normal forms results in a less perfectly designed database, but the functionality of your database is not affected by avoiding them.

The advanced normal forms are as follows:

4th normal form: Boyce Codd Normal Form (BCNF)

  • Eliminate relations with multi-valued dependencies.

5th normal form:

  • Create relations that cannot be further decomposed.