I know that 3NF makes a database more efficient and stores data according to relevance, which is more logical, but are there other advantages? and are there any disadvantages?
Add a comment
|
2 Answers
Third normal form(3NF) includes the rules of the lower level normal form, such as second (2NF) and first normal form (1NF). Similarly, Boyce/Codd normal form (BCNF) includes the rules of the lower level such as 3NF, 2NF and so on (see here). Normalization provides both advantages and disadvantages, some of them are mentioned below.
Advantages of normalization:
Reduced data redundancy:
In each level of normalization a type of data redundancy (same data present in more than once) removed from the model. Once the redundancy is removed, it is easy to change the data since data is present in only one place.
Increased data quality:
By applying business rules, there is a smaller chance in storing unwanted data in the table. Also there is less need for storing null values.
Capturing complete business requirements:
In normalization we have to ask a lot of questions to business people, so our understanding of the business becomes stronger and requirements of the business are easy to capture.
Data modification anomalies (Insert, delete and update) are reduced.
Normalization splits a entity into a smaller number of entities, so you will have more smaller tables, so it is easier for sorting, indexing and searching.
Since there are many smaller tables, you will have many clustered indexes, it will help you in query tuning.
A normalized data model can be easier to modify and maintain.
Disadvantages
With many number tables and joins in between those tables slow down the performance of the database.
Having a large number of tables consumes more development time for implementation.
A completely normalized database needs clear and broad understanding of the business, it takes more time to analyse and understand the business. I hope this will help you. Thank you.
answered Jan 5, 2016 at 15:24
Except a logical aspect - there is a performance one - you divide data to a smaller tables. Smaller tables = smaller indexes and faster search options, less memory and IO consumption.
One disadvantage, that I can think about is longer code - lots of joins to query tables. Another one is that divided data is not the best option for OLAP processes, that is why sometimes you'll see not normalized tables at Data Warehouse environments.
