What adverse effects does a table have when it’s not even in 1nf?


What adverse effects would a table have if it's not even in 1nf ?

I googled and read the article on Wikipedia but I couldn't find exactly why a table should be in 1nf.
Also, I couldn't find what horrible things might happen if a table is not in 1nf.

So other than being able to use simple queries , what monsters would crawl out of the tables while I sleep happily at home if my tables weren't in 1nf ?

Best Answer

No monsters will crawl out of the tables that are not in first normal form, however, you may turn into one when a simple change request requires major remodelling of your table structure. Let me go to this in further detail.

According to Wikipedia article on first normal form, it helps to

  • Eliminate repeating groups in individual tables.
  • Create a separate table for each set of related data
  • Identify each set of related data with a primary key

Now let's assume that Stack Exchange would have ignored this advice when designing their database back-end and have stored all their questions in one big table. When a tag gets added to a question a new row gets added to the question table. This all works fine until of the moderators has noticed that the SQL Server tag has been used inconsistently(SQL Server, Microsoft SQL, Microsoft SQ Server,etc.) and wants to clean this up.

In a normalised form this could be achieved by updating and deleting a handful of rows, however, without normalising you will need to update every single question that has been tagged incorrectly. This can result in locking of the database and freezing of the site. This means the moderator can't simply change the tags immediately using the moderator tools, and instead these changes would have to be queued until the site is less busy.

This normalised form would also have resulted in huge performance issues as the site grows in size. As each question can have multiple tags there would be many duplicate rows in your question table and it would get slower and slower to query. You would, for example, quickly run into problems querying all available tags as you would have to query the entire question table instead of the tag table.