Right Database normalization?

Leave a comment

May 18, 2014 by Ozgur Ozden

As we all know that the main idea of normalization is to remove the redundant data, assign a unique key for identification and setting proper relationships between the tables. As a result database can be more reliable and easy to manage. Also when you do some changes, you do not need to do the same changes in different places with the help of the normalization. There are different levels of normalization such as 1NF, 2NF, 3NF, 4NF, … , BCNF and 3NF usually accepted as prefered level. But database designer must keep in mind that every business requires different understandings and different database design. One design might not be suitable for other.

But the “right” and suitable design may not require the use normalization in some specific cases especially normalization outweighed the cost. So in certain cases it might be beneficial to denormalize. Let us try to investigate these cases.

Joining tables: İf the database you are using is heavily loaded and complex, if you you are using many queries and joining these tables to reduce into a single one, joining takes considerable amount of time. In this case denormalizing and re-structuring your database depending on your commonly used queries might be good choice.
As we have described above , if the database is complicated some stages of normalization becomes very difficult . At this stage designer should reconsider the normalization. Normally 3NF is the desired level of normalization and if the database designer is having difficulties to go to the higher levels, normalization should be reconsidered.
Sometimes normalization might not be necessary if we are storing immutable, constant data in the database such as price list, past transactions, etc… These are constant data values and normalizing them might be a lost of resources.

So, normalization is the desired and accepted database design process but in some certain cases it might be beneficial to seek the solutions out of the box. In huge and complex databases normalization causes problems when you try to join many tables. According to Gregorio in his post named “the emergence of megadata” big companies like eBay, google and Amazon already started to denormalize some of their databases to increase their scalability.

References:

Coronel, C., Morris, S. & Rob, P. (2013), Database Systems: Design, Implementation, and Management, 10thed, Boston, MA: Course Technology, Cengage Learning.
Atwood (2008) Maybe Normalizing Isn’t Normal (Online) Available at: http://www.codinghorror.com/blog/2008/07/maybe-normalizing-isnt-normal.html [accessed at: 5.10.2013]
When Not to Normalize your SQL Database (2007) (Online) Avaiable at: http://www.25hoursaday.com/weblog/CommentView.aspx?guid=cc0e740c-a828-4b9d-b244-4ee96e2fad4b [accessed at: 5.10.2013]
Joe Gregorio (2007) Megadata (Online) Avaiable at: http://bitworking.org/news/158/ETech-07-Summary-Part-2-MegaData [accessed at: 4.10.2013]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: