A table is in fourth normal form if no row contains two or more independent multi-valued facts about an entity.
Consider these entities: employees, skills, and languages. An employee can
have several skills and know several languages. There are two relationships, one
between employees and skills, and one between employees and languages. A table
is not in fourth normal form if it represents both relationships, as in the
following example:
Table Violating Fourth Normal Form
EMPNO (Primary Key) | SKILL (Primary Key) | LANGUAGE (Primary Key) |
000130 | Data Modelling | English |
000130 | Database Design | English |
000130 | Application Design | English |
000130 | Data Modelling | Spanish |
000130 | Database Design | Spanish |
000130 | Application Design | Spanish |
Instead, the relationships should be represented in two tables:
EMPLOYEE_SKILL Table Conforming to Fourth Normal Form
EMPNO (Primary Key) | SKILL (Primary Key) |
000130 | Data Modelling |
000130 | Database Design |
000130 | Application Design |
EMPLOYEE_LANGUAGE Table Conforming to Fourth Normal Form
EMPNO (Primary Key) | LANGUAGE (Primary Key) |
000130 | English |
000130 | Spanish |
If, however, the attributes are interdependent (that is, the employee applies certain languages only to certain skills), the table should not be split.
A good strategy when designing a database is to arrange all data in tables that are in fourth normal form, and then to decide whether the results give you an acceptable level of performance. If they do not, you can rearrange the data in tables that are in third normal form, and then reassess performance.