Data Normalization

Data normalization is the structuring of data tables to maximize usability and quality. It is mainly used in the context of relational databases, typically identified as SQL databases. There are different levels of normalization, but third normal form (3NF) or the slightly more strict Boyce-Codd normal form (BCNF) are the most common.

The quality of a database greatly increases when the data is normalized. Otherwise, you have large amounts of duplicate data. You can never be sure which data is the intended value.

The alternatives to relational database tables are hierarchies represented in XML or JSON. These appeal to young programmers because they seem more flexible and newer than relational databases. JSON is useful for passing small amounts of data between programming units, such as browser code and a server application, but relational data tends to be low quality and difficult to use. Relational databases are much easier to use for statistics and quality checking than JSON databases.

The example shows four simplified patient EHR (Electronic Health Record) tables. The first three are normalized. The last is for reporting and it contains derived data, such as the patient age in years. The roster table allows the core data to stay as clean as possible.

The associated image is about the size of a page and is smaller than non-technical illustrations on this site.