Normalization of data

Levels of normalization of data structure…

Author

Søren O’Neill & Steen Harsted

Published

January 6, 2024


Observing a tidy data format is sufficient for most purposes.

A more comprehensive way to approach data structure is called data normalization: “Normalization is a process that database designers use to eliminate data redundancy, improve data integrity, and enhance the overall efficiency of the database.” 1

The normalization of data has nothing to do with the normal (Gaussian) distribution.

Data normalization is not a case of either/or – data can be normalized to different levels, depending on your needs. In this text, we will deal only with levels 1 and 2.

1 First Normal Form – 1NF

The First Normal Form (of data normalization) is roughly similar to the Tidy data format and requires that:

  • Each column represents one variable
  • Each variable contains atomic values – i.e. the smallest (indivisible) unit of information
  • Variables do not contain arrays of atomic values
  • The data set does not contain repeating groups of similar variables

Look at the different examples of structures of the same data on the tabs below in light of the requirements for First Normal Form data listed above – and read the comment below each table:

Table 1: A
subject_id subject_name measurement test_day test type
1 John 501,927 MON,FRI a,b Blood,Saliva
2 Billy 221,5 MON,TUE a,b Blood,Saliva
3 Joan 963,158 TUE,TUE a,b Blood,Saliva
4 Clare 711,131 WED,MON a,b Blood,Saliva
5 Peter 743,919 TUE,TUE a,b Blood,Saliva
6 Maggy 331,976 THU,TUE a,b Blood,Saliva
7 Jack 26,153 TUE,FRI a,b Blood,Saliva
8 Suzy 556,329 THU,THU a,b Blood,Saliva
9 Billy 914,936 TUE,MON a,b Blood,Saliva
10 Mary 889,645 MON,FRI a,b Blood,Saliva

More than one value is stored per cell in columns measurement:type. In other words, each cell consists of an array (or a series of) values, separated by a comma. This does not conform to 1NF.

Table 2: B
subject_id subject_name test_a test_b
1 John 501 (MON : Blood) 927 (FRI : Saliva)
2 Billy 221 (MON : Blood) 5 (TUE : Saliva)
3 Joan 963 (TUE : Blood) 158 (TUE : Saliva)
4 Clare 711 (WED : Blood) 131 (MON : Saliva)
5 Peter 743 (TUE : Blood) 919 (TUE : Saliva)
6 Maggy 331 (THU : Blood) 976 (TUE : Saliva)
7 Jack 26 (TUE : Blood) 153 (FRI : Saliva)
8 Suzy 556 (THU : Blood) 329 (THU : Saliva)
9 Billy 914 (TUE : Blood) 936 (MON : Saliva)
10 Mary 889 (MON : Blood) 645 (FRI : Saliva)

The data is not atomic, i.e. indivisible. The cells of columns test_a and test_b contain three different values (measurement, weekday, and type). This does not conform to 1NF.

Table 3: C
subject_id subject_name test_a test_b day_of_test_a day_of_test_b test_a_type test_b_type
1 John 501 927 MON FRI Blood Saliva
2 Billy 221 5 MON TUE Blood Saliva
3 Joan 963 158 TUE TUE Blood Saliva
4 Clare 711 131 WED MON Blood Saliva
5 Peter 743 919 TUE TUE Blood Saliva
6 Maggy 331 976 THU TUE Blood Saliva
7 Jack 26 153 TUE FRI Blood Saliva
8 Suzy 556 329 THU THU Blood Saliva
9 Billy 914 936 TUE MON Blood Saliva
10 Mary 889 645 MON FRI Blood Saliva

The data is atomic, and there is only one data point per cell. However, the data set contains repeating groups of similar variables (‘a’ and ‘b’ – look at the columns names). This does not conform to 1NF.

Table 4: D
subject_id subject_name test_id type day measurement
1 John a Blood MON 501
1 John b Saliva FRI 927
2 Billy a Blood MON 221
2 Billy b Saliva TUE 5
3 Joan a Blood TUE 963
3 Joan b Saliva TUE 158
4 Clare a Blood WED 711
4 Clare b Saliva MON 131
5 Peter a Blood TUE 743
5 Peter b Saliva TUE 919
6 Maggy a Blood THU 331
6 Maggy b Saliva TUE 976
7 Jack a Blood TUE 26
7 Jack b Saliva FRI 153
8 Suzy a Blood THU 556
8 Suzy b Saliva THU 329
9 Billy a Blood TUE 914
9 Billy b Saliva MON 936
10 Mary a Blood MON 889
10 Mary b Saliva FRI 645

This data conforms to the First Normal Form (1NF) data structure.

2 Second Normal Form – 2NF

The Second Normal Form requires that data conforms to the 1NF requirements and additionally:

  • Data contains a single-column primary key.
  • That all non-key variables ‘depend’ on the entire primary key.

..the meaning of this will become clearer when you work through examples A, B and C below.

Table 5: A
subject_id subject_name test_id type day measurement
1 John a Blood MON 501
1 John b Saliva FRI 927
2 Billy a Blood MON 221
2 Billy b Saliva TUE 5
3 Joan a Blood TUE 963
3 Joan b Saliva TUE 158
4 Clare a Blood WED 711
4 Clare b Saliva MON 131
5 Peter a Blood TUE 743
5 Peter b Saliva TUE 919
6 Maggy a Blood THU 331
6 Maggy b Saliva TUE 976
7 Jack a Blood TUE 26
7 Jack b Saliva FRI 153
8 Suzy a Blood THU 556
8 Suzy b Saliva THU 329
9 Billy a Blood TUE 914
9 Billy b Saliva MON 936
10 Mary a Blood MON 889
10 Mary b Saliva FRI 645

The data does not contain a single-column primary key. Each observation is unique and identifiable by the (compound) primary key subject_id + test_id.

Table 6: B
key subject_id subject_name test_id type day measurement
A 1 John a Blood MON 501
B 1 John b Saliva FRI 927
C 2 Billy a Blood MON 221
D 2 Billy b Saliva TUE 5
E 3 Joan a Blood TUE 963
F 3 Joan b Saliva TUE 158
G 4 Clare a Blood WED 711
H 4 Clare b Saliva MON 131
I 5 Peter a Blood TUE 743
J 5 Peter b Saliva TUE 919
K 6 Maggy a Blood THU 331
L 6 Maggy b Saliva TUE 976
M 7 Jack a Blood TUE 26
N 7 Jack b Saliva FRI 153
O 8 Suzy a Blood THU 556
P 8 Suzy b Saliva THU 329
Q 9 Billy a Blood TUE 914
R 9 Billy b Saliva MON 936
S 10 Mary a Blood MON 889
T 10 Mary b Saliva FRI 645

The data does contains a single-column primary key (key), but all non-key variables do not ‘depend’ on the entire primary key. Specifically, the variable type is contingent exclusively on test_id. In this example, there is a very simple \(1:1\) relationship between test_id and type (test ‘a’ is always ‘Blood’, and ‘b’ always ‘Saliva’). If that is always the case, then type is not dependent upon the unique identifier key, but only the test_id variable.

Data frame #1

Table 7: C1
key subject_id subject_name test_id day measurement
A 1 John a MON 501
B 1 John b FRI 927
C 2 Billy a MON 221
D 2 Billy b TUE 5
E 3 Joan a TUE 963
F 3 Joan b TUE 158
G 4 Clare a WED 711
H 4 Clare b MON 131
I 5 Peter a TUE 743
J 5 Peter b TUE 919
K 6 Maggy a THU 331
L 6 Maggy b TUE 976
M 7 Jack a TUE 26
N 7 Jack b FRI 153
O 8 Suzy a THU 556
P 8 Suzy b THU 329
Q 9 Billy a TUE 914
R 9 Billy b MON 936
S 10 Mary a MON 889
T 10 Mary b FRI 645

Data frame #2

Table 8: C2
test_id type
a Blood
b Saliva

This data structure conforms to the Second Normal Form (2NF) data structure. The data has been split into two separate tables, with no redundancy of information. The variable test_id in the first, larger data set, is related to test_id in the second, smaller data set. This structure makes data much easier to survey, when the amount of data grows large. Conversely, if data from different tables are needed for a given analysis, the data has to wrangled first (specifically merge with a join command). Note that if there is not a fixed and invariable relationship between typeand test_id, the data should not be split up into different tables, but remain in a structure similar to Example B.

3 Further Normal Forms

For most statistical analyses 2NF will suffice – in fact, you can probably get along fine with 1NF. If your data set is very large and complex, requiring a relational database system, look online for further details about data normalization - e.g. this link

At observere et tidy data-format er tilstrækkeligt til de fleste formål.

En mere omfattende måde at tilgå datastruktur på kaldes data-normalisering: “Normalisering er en proces, som databasespecialister bruger til at eliminere redundans, forbedre dataintegritet og øge databaseeffektiviteten.” 2

Normalisering af data har intet at gøre med den normale (Gaussiske) fordeling.

Data-normalisering er ikke et enten/eller-valg – data kan normaliseres til forskellige niveauer afhængigt af behov. I denne tekst vil vi kun beskæftige os med niveau 1 og 2.

4 Første Normalform – 1NF

Første normalform (1NF) i data-normalisering svarer nogenlunde til tidy data-formatet og kræver, at:

  • Hver kolonne repræsenterer en variabel
  • Hver variabel indeholder atomiske værdier – dvs. de mindste (uinddelbare) informationsenheder
  • Variabler ikke indeholder arrays af atomiske værdier
  • Datasættet ikke indeholder gentagende grupper af lignende variabler

Se på de forskellige eksempler på datastrukturer i fanerne nedenfor i forhold til kravene for data i første normalform – og læs kommentaren under hver tabel:

Table 9: A
subject_id subject_name measurement test_day test type
1 John 501,927 MON,FRI a,b Blood,Saliva
2 Billy 221,5 MON,TUE a,b Blood,Saliva
3 Joan 963,158 TUE,TUE a,b Blood,Saliva
4 Clare 711,131 WED,MON a,b Blood,Saliva
5 Peter 743,919 TUE,TUE a,b Blood,Saliva
6 Maggy 331,976 THU,TUE a,b Blood,Saliva
7 Jack 26,153 TUE,FRI a,b Blood,Saliva
8 Suzy 556,329 THU,THU a,b Blood,Saliva
9 Billy 914,936 TUE,MON a,b Blood,Saliva
10 Mary 889,645 MON,FRI a,b Blood,Saliva

Mere end én værdi er lagret pr. celle i kolonnerne measurement:type. Med andre ord, hver celle består af et array (eller en serie af) værdier, adskilt af komma. Dette er ikke i overensstemmelse med 1NF.

Table 10: B
subject_id subject_name test_a test_b
1 John 501 (MON : Blood) 927 (FRI : Saliva)
2 Billy 221 (MON : Blood) 5 (TUE : Saliva)
3 Joan 963 (TUE : Blood) 158 (TUE : Saliva)
4 Clare 711 (WED : Blood) 131 (MON : Saliva)
5 Peter 743 (TUE : Blood) 919 (TUE : Saliva)
6 Maggy 331 (THU : Blood) 976 (TUE : Saliva)
7 Jack 26 (TUE : Blood) 153 (FRI : Saliva)
8 Suzy 556 (THU : Blood) 329 (THU : Saliva)
9 Billy 914 (TUE : Blood) 936 (MON : Saliva)
10 Mary 889 (MON : Blood) 645 (FRI : Saliva)

Data er ikke atomisk, dvs. udelelig. Cellerne i kolonnerne test_a og test_b indeholder tre forskellige værdier (måling, ugedag og type). Dette er ikke i overensstemmelse med 1NF.

Table 11: C
subject_id subject_name test_a test_b day_of_test_a day_of_test_b test_a_type test_b_type
1 John 501 927 MON FRI Blood Saliva
2 Billy 221 5 MON TUE Blood Saliva
3 Joan 963 158 TUE TUE Blood Saliva
4 Clare 711 131 WED MON Blood Saliva
5 Peter 743 919 TUE TUE Blood Saliva
6 Maggy 331 976 THU TUE Blood Saliva
7 Jack 26 153 TUE FRI Blood Saliva
8 Suzy 556 329 THU THU Blood Saliva
9 Billy 914 936 TUE MON Blood Saliva
10 Mary 889 645 MON FRI Blood Saliva

Data er atomisk, og der er kun én datapunkt pr. celle. Dog indeholder datasættet gentagende grupper af lignende variabler (’a’ og ’b’ – se kolonnenavnene). Dette er ikke i overensstemmelse med 1NF.

Table 12: D
subject_id subject_name test_id type day measurement
1 John a Blood MON 501
1 John b Saliva FRI 927
2 Billy a Blood MON 221
2 Billy b Saliva TUE 5
3 Joan a Blood TUE 963
3 Joan b Saliva TUE 158
4 Clare a Blood WED 711
4 Clare b Saliva MON 131
5 Peter a Blood TUE 743
5 Peter b Saliva TUE 919
6 Maggy a Blood THU 331
6 Maggy b Saliva TUE 976
7 Jack a Blood TUE 26
7 Jack b Saliva FRI 153
8 Suzy a Blood THU 556
8 Suzy b Saliva THU 329
9 Billy a Blood TUE 914
9 Billy b Saliva MON 936
10 Mary a Blood MON 889
10 Mary b Saliva FRI 645

Dette data er i overensstemmelse med første normalform (1NF).

5 Anden Normalform – 2NF

Anden normalform kræver, at data opfylder 1NF-kravene og yderligere:

  • Data indeholder en enkelt-kolonne primærnøgle.
  • Alle ikke-nøgle-variabler afhænger af hele primærnøglen.

… betydningen af dette vil blive tydeligere, når du arbejder dig igennem eksemplerne A, B og C nedenfor.

Table 13: A
subject_id subject_name test_id type day measurement
1 John a Blood MON 501
1 John b Saliva FRI 927
2 Billy a Blood MON 221
2 Billy b Saliva TUE 5
3 Joan a Blood TUE 963
3 Joan b Saliva TUE 158
4 Clare a Blood WED 711
4 Clare b Saliva MON 131
5 Peter a Blood TUE 743
5 Peter b Saliva TUE 919
6 Maggy a Blood THU 331
6 Maggy b Saliva TUE 976
7 Jack a Blood TUE 26
7 Jack b Saliva FRI 153
8 Suzy a Blood THU 556
8 Suzy b Saliva THU 329
9 Billy a Blood TUE 914
9 Billy b Saliva MON 936
10 Mary a Blood MON 889
10 Mary b Saliva FRI 645

Data indeholder ikke en enkelt-kolonne primærnøgle. Hver observation er unik og identificerbar ved den (sammensatte) primærnøgle subject_id + test_id.

Table 14: B
key subject_id subject_name test_id type day measurement
A 1 John a Blood MON 501
B 1 John b Saliva FRI 927
C 2 Billy a Blood MON 221
D 2 Billy b Saliva TUE 5
E 3 Joan a Blood TUE 963
F 3 Joan b Saliva TUE 158
G 4 Clare a Blood WED 711
H 4 Clare b Saliva MON 131
I 5 Peter a Blood TUE 743
J 5 Peter b Saliva TUE 919
K 6 Maggy a Blood THU 331
L 6 Maggy b Saliva TUE 976
M 7 Jack a Blood TUE 26
N 7 Jack b Saliva FRI 153
O 8 Suzy a Blood THU 556
P 8 Suzy b Saliva THU 329
Q 9 Billy a Blood TUE 914
R 9 Billy b Saliva MON 936
S 10 Mary a Blood MON 889
T 10 Mary b Saliva FRI 645

Data indeholder en enkelt-kolonne primærnøgle (key), men alle ikke-nøgle-variabler afhænger ikke af hele primærnøglen. Specifikt afhænger variablen type udelukkende af test_id. I dette eksempel er der et meget simpelt \(1:1\)-forhold mellem test_id og type (test ‘a’ er altid ‘Blod’, og ‘b’ altid ‘Spyt’). Hvis det altid er tilfældet, er type ikke afhængig af primærnøglen key men kun af test_id.

Datasæt #1

Table 15: C1
key subject_id subject_name test_id day measurement
A 1 John a MON 501
B 1 John b FRI 927
C 2 Billy a MON 221
D 2 Billy b TUE 5
E 3 Joan a TUE 963
F 3 Joan b TUE 158
G 4 Clare a WED 711
H 4 Clare b MON 131
I 5 Peter a TUE 743
J 5 Peter b TUE 919
K 6 Maggy a THU 331
L 6 Maggy b TUE 976
M 7 Jack a TUE 26
N 7 Jack b FRI 153
O 8 Suzy a THU 556
P 8 Suzy b THU 329
Q 9 Billy a TUE 914
R 9 Billy b MON 936
S 10 Mary a MON 889
T 10 Mary b FRI 645

Datasæt #2

Table 16: C2
test_id type
a Blood
b Saliva

Denne datastruktur er i overensstemmelse med anden normalform (2NF). Data er delt op i to separate tabeller uden redundans af information. Variablen test_id i det første, større datasæt, er relateret til test_id i det andet, mindre datasæt. Denne struktur gør data meget lettere at overskue, når datamængden vokser sig stor. Omvendt skal data fra forskellige tabeller behandles først (specifikt ved at anvende en join-kommando), hvis data fra flere tabeller er nødvendigt til en given analyse. Bemærk, at hvis der ikke er et fast og uforanderligt \(1:1\) forhold mellem test_id og type skal data ikke splittes op i to tabeller, men forblive i formen som Eksempel B.

6 Yderligere normalformer

Til de fleste statistiske analyser vil 2NF være tilstrækkeligt – faktisk kan du sandsynligvis klare dig fint med 1NF. Hvis dit datasæt er meget stort og komplekst og kræver et relationelt databasesystem, kan du finde yderligere detaljer om data-normalisering online – f.eks. denne side.