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 841,458 WED,WED a,b Blood,Saliva
2 Billy 356,663 TUE,TUE a,b Blood,Saliva
3 Joan 88,301 TUE,THU a,b Blood,Saliva
4 Clare 343,392 THU,MON a,b Blood,Saliva
5 Peter 907,545 FRI,FRI a,b Blood,Saliva
6 Maggy 209,333 WED,FRI a,b Blood,Saliva
7 Jack 770,659 MON,TUE a,b Blood,Saliva
8 Suzy 278,810 TUE,TUE a,b Blood,Saliva
9 Billy 574,96 MON,MON a,b Blood,Saliva
10 Mary 732,146 THU,MON 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 841 (WED : Blood) 458 (WED : Saliva)
2 Billy 356 (TUE : Blood) 663 (TUE : Saliva)
3 Joan 88 (TUE : Blood) 301 (THU : Saliva)
4 Clare 343 (THU : Blood) 392 (MON : Saliva)
5 Peter 907 (FRI : Blood) 545 (FRI : Saliva)
6 Maggy 209 (WED : Blood) 333 (FRI : Saliva)
7 Jack 770 (MON : Blood) 659 (TUE : Saliva)
8 Suzy 278 (TUE : Blood) 810 (TUE : Saliva)
9 Billy 574 (MON : Blood) 96 (MON : Saliva)
10 Mary 732 (THU : Blood) 146 (MON : 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 841 458 WED WED Blood Saliva
2 Billy 356 663 TUE TUE Blood Saliva
3 Joan 88 301 TUE THU Blood Saliva
4 Clare 343 392 THU MON Blood Saliva
5 Peter 907 545 FRI FRI Blood Saliva
6 Maggy 209 333 WED FRI Blood Saliva
7 Jack 770 659 MON TUE Blood Saliva
8 Suzy 278 810 TUE TUE Blood Saliva
9 Billy 574 96 MON MON Blood Saliva
10 Mary 732 146 THU MON 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 WED 841
1 John b Saliva WED 458
2 Billy a Blood TUE 356
2 Billy b Saliva TUE 663
3 Joan a Blood TUE 88
3 Joan b Saliva THU 301
4 Clare a Blood THU 343
4 Clare b Saliva MON 392
5 Peter a Blood FRI 907
5 Peter b Saliva FRI 545
6 Maggy a Blood WED 209
6 Maggy b Saliva FRI 333
7 Jack a Blood MON 770
7 Jack b Saliva TUE 659
8 Suzy a Blood TUE 278
8 Suzy b Saliva TUE 810
9 Billy a Blood MON 574
9 Billy b Saliva MON 96
10 Mary a Blood THU 732
10 Mary b Saliva MON 146

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 WED 841
1 John b Saliva WED 458
2 Billy a Blood TUE 356
2 Billy b Saliva TUE 663
3 Joan a Blood TUE 88
3 Joan b Saliva THU 301
4 Clare a Blood THU 343
4 Clare b Saliva MON 392
5 Peter a Blood FRI 907
5 Peter b Saliva FRI 545
6 Maggy a Blood WED 209
6 Maggy b Saliva FRI 333
7 Jack a Blood MON 770
7 Jack b Saliva TUE 659
8 Suzy a Blood TUE 278
8 Suzy b Saliva TUE 810
9 Billy a Blood MON 574
9 Billy b Saliva MON 96
10 Mary a Blood THU 732
10 Mary b Saliva MON 146

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 WED 841
B 1 John b Saliva WED 458
C 2 Billy a Blood TUE 356
D 2 Billy b Saliva TUE 663
E 3 Joan a Blood TUE 88
F 3 Joan b Saliva THU 301
G 4 Clare a Blood THU 343
H 4 Clare b Saliva MON 392
I 5 Peter a Blood FRI 907
J 5 Peter b Saliva FRI 545
K 6 Maggy a Blood WED 209
L 6 Maggy b Saliva FRI 333
M 7 Jack a Blood MON 770
N 7 Jack b Saliva TUE 659
O 8 Suzy a Blood TUE 278
P 8 Suzy b Saliva TUE 810
Q 9 Billy a Blood MON 574
R 9 Billy b Saliva MON 96
S 10 Mary a Blood THU 732
T 10 Mary b Saliva MON 146

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 WED 841
B 1 John b WED 458
C 2 Billy a TUE 356
D 2 Billy b TUE 663
E 3 Joan a TUE 88
F 3 Joan b THU 301
G 4 Clare a THU 343
H 4 Clare b MON 392
I 5 Peter a FRI 907
J 5 Peter b FRI 545
K 6 Maggy a WED 209
L 6 Maggy b FRI 333
M 7 Jack a MON 770
N 7 Jack b TUE 659
O 8 Suzy a TUE 278
P 8 Suzy b TUE 810
Q 9 Billy a MON 574
R 9 Billy b MON 96
S 10 Mary a THU 732
T 10 Mary b MON 146

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 841,458 WED,WED a,b Blood,Saliva
2 Billy 356,663 TUE,TUE a,b Blood,Saliva
3 Joan 88,301 TUE,THU a,b Blood,Saliva
4 Clare 343,392 THU,MON a,b Blood,Saliva
5 Peter 907,545 FRI,FRI a,b Blood,Saliva
6 Maggy 209,333 WED,FRI a,b Blood,Saliva
7 Jack 770,659 MON,TUE a,b Blood,Saliva
8 Suzy 278,810 TUE,TUE a,b Blood,Saliva
9 Billy 574,96 MON,MON a,b Blood,Saliva
10 Mary 732,146 THU,MON 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 841 (WED : Blood) 458 (WED : Saliva)
2 Billy 356 (TUE : Blood) 663 (TUE : Saliva)
3 Joan 88 (TUE : Blood) 301 (THU : Saliva)
4 Clare 343 (THU : Blood) 392 (MON : Saliva)
5 Peter 907 (FRI : Blood) 545 (FRI : Saliva)
6 Maggy 209 (WED : Blood) 333 (FRI : Saliva)
7 Jack 770 (MON : Blood) 659 (TUE : Saliva)
8 Suzy 278 (TUE : Blood) 810 (TUE : Saliva)
9 Billy 574 (MON : Blood) 96 (MON : Saliva)
10 Mary 732 (THU : Blood) 146 (MON : 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 841 458 WED WED Blood Saliva
2 Billy 356 663 TUE TUE Blood Saliva
3 Joan 88 301 TUE THU Blood Saliva
4 Clare 343 392 THU MON Blood Saliva
5 Peter 907 545 FRI FRI Blood Saliva
6 Maggy 209 333 WED FRI Blood Saliva
7 Jack 770 659 MON TUE Blood Saliva
8 Suzy 278 810 TUE TUE Blood Saliva
9 Billy 574 96 MON MON Blood Saliva
10 Mary 732 146 THU MON 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 WED 841
1 John b Saliva WED 458
2 Billy a Blood TUE 356
2 Billy b Saliva TUE 663
3 Joan a Blood TUE 88
3 Joan b Saliva THU 301
4 Clare a Blood THU 343
4 Clare b Saliva MON 392
5 Peter a Blood FRI 907
5 Peter b Saliva FRI 545
6 Maggy a Blood WED 209
6 Maggy b Saliva FRI 333
7 Jack a Blood MON 770
7 Jack b Saliva TUE 659
8 Suzy a Blood TUE 278
8 Suzy b Saliva TUE 810
9 Billy a Blood MON 574
9 Billy b Saliva MON 96
10 Mary a Blood THU 732
10 Mary b Saliva MON 146

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 WED 841
1 John b Saliva WED 458
2 Billy a Blood TUE 356
2 Billy b Saliva TUE 663
3 Joan a Blood TUE 88
3 Joan b Saliva THU 301
4 Clare a Blood THU 343
4 Clare b Saliva MON 392
5 Peter a Blood FRI 907
5 Peter b Saliva FRI 545
6 Maggy a Blood WED 209
6 Maggy b Saliva FRI 333
7 Jack a Blood MON 770
7 Jack b Saliva TUE 659
8 Suzy a Blood TUE 278
8 Suzy b Saliva TUE 810
9 Billy a Blood MON 574
9 Billy b Saliva MON 96
10 Mary a Blood THU 732
10 Mary b Saliva MON 146

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 WED 841
B 1 John b Saliva WED 458
C 2 Billy a Blood TUE 356
D 2 Billy b Saliva TUE 663
E 3 Joan a Blood TUE 88
F 3 Joan b Saliva THU 301
G 4 Clare a Blood THU 343
H 4 Clare b Saliva MON 392
I 5 Peter a Blood FRI 907
J 5 Peter b Saliva FRI 545
K 6 Maggy a Blood WED 209
L 6 Maggy b Saliva FRI 333
M 7 Jack a Blood MON 770
N 7 Jack b Saliva TUE 659
O 8 Suzy a Blood TUE 278
P 8 Suzy b Saliva TUE 810
Q 9 Billy a Blood MON 574
R 9 Billy b Saliva MON 96
S 10 Mary a Blood THU 732
T 10 Mary b Saliva MON 146

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 WED 841
B 1 John b WED 458
C 2 Billy a TUE 356
D 2 Billy b TUE 663
E 3 Joan a TUE 88
F 3 Joan b THU 301
G 4 Clare a THU 343
H 4 Clare b MON 392
I 5 Peter a FRI 907
J 5 Peter b FRI 545
K 6 Maggy a WED 209
L 6 Maggy b FRI 333
M 7 Jack a MON 770
N 7 Jack b TUE 659
O 8 Suzy a TUE 278
P 8 Suzy b TUE 810
Q 9 Billy a MON 574
R 9 Billy b MON 96
S 10 Mary a THU 732
T 10 Mary b MON 146

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.