4th Form of Database Normalization Design
What are Normalization and its needs?
In any typical database, there are many ways data can be redundant and heavy. Normalization is a technique where we can optimize the database design to follow some rules and avoid redundancy
Any type of data redundancy is problematic as being said Inserting, Deletion, and Updation any record will cause us either more time or resources to process. I am hoping you must have a basic idea of normalization and a general idea of database design so we can proceed with the 4NF form
Previous Normalization Forms Brief
1NF — 1NF is a basic database schema that should be there in any database otherwise it is considered a bad database design. Rules of the 1NF are
- There should be no duplicate column names
- Order of insertion is not required
- A Column should only store single datatype records
- There should not be a column that holds multiple values(Array or tuples)
2NF — 2NF is the upper hand of 1NF where it should have everything as 1NF plus there should be no partial dependency
Partial dependency is any composite-primary key in a table should not be able to fetch other column using part of the primary key
3NF —3NF is the upper hand of 2NF where it should have everything as 1NF plus there should be no transitive dependency
transitive dependency is non prime attribute in a table should not be able to fetch any other non prime attribute
BCNF — Also called 3.5NF should be able to satisfy everything in 3NF and there should be no dependency exist that satisfy non-prime → prime attribute
What is 4NF?
Now, we have a basic idea of BCNF, we can learn about 4NF, in any given database it is considered good database design if a database is good till BCNF at least and 4NF is an extension to it where we make a database even less redundant by removing multi-valued dependencies
What is a multi-valued dependency?
the formal definition of multi-valued dependency is there exist at least 2 independent columns dependent on the primary key containing different values is called multi-valued dependency.
Let's try to understand this in more easy terms. Consider an example, we have an enrollment platform where any employee can enroll their expertise and later they get in some sort of examination There is one person(Sam) who also knows more than one language so we created enrollment based on industry and language known
Now we created a table called enrollment where EnrollmentId is the primary key and defines the columns as following
Here we have user U1(Sam) who has worked in Software as well as in Electronics and knows 2 languages English and German. At first, this table satisfy everything till BCNF but there is a major fallback if you can notice
Since the same knows 2 different languages and worked in 2 different industries that make them isolated which means we can generate 4 combinations with the fields i.e Sam can also enroll with Software and German language or Hardware and English language
Considering there is nothing wrong with BCNF but it still considers a poor table schema since it allows unwanted redundancy of the columns. The redundancy will grow more if there are more isolated columns in the picture
How does 4NF handle multi-valued dependencies?
Just like other NF, 4NF will also propose a new table that holds the data but the number of tables will differ based on what kind of functional dependencies are already present inside a table(This can be seen in more exhaustive examples where more than 10-20 columns present)
But in our example, We can create two tables Language known and Industry and link to the User instance by using that it will satisfy the 4NF conditions
Conclusion
It is really hard to achieve all normalization in all tables but one must analyze the table before publishing them for the schema implementation.
I hope you learn something new Today and apply the knowledge in your next project. Happy Coding!
Go composable: Build apps faster like Lego
Bit is an open-source tool for building apps in a modular and collaborative way. Go composable to ship faster, more consistently, and easily scale.
Build apps, pages, user-experiences and UIs as standalone components. Use them to compose new apps and experiences faster. Bring any framework and tool into your workflow. Share, reuse, and collaborate to build together.
Help your team with:
