Violation Of 1NF and how to deal with it

What is 1 NF?

The first normal form (1NF) is one of the most violated rules while designing and developing a database.The First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain.

The benefits of Normalization are:

• Greater overall database organization
• Reduction of redundant data
• Data consistency within the database
• A much more flexible database design
• A better handle on database security

In simpler terms, A column must contain a single value and not multiple values separated by a delimiter of the designer’s choice.

From the above sample table, the first record shows it is a valid record as per 1NF, whereas, the 2nd record is in violation of the rule as it consists of multiple values with a delimiter.

Most often, we come across systems designed that has violated this rule. The most common reason given for violating the rule is that space required to store data is minimum and at times remove redundancy as well. Given the fact that many systems use such a technique, a database developer has to know the workarounds with such a design.

With some experience working on the SQL Server, splitting the column values to bring to a tabular form is my preferred way of dealing with such a system.

The Split Function:

CREATE FUNCTION [dbo].[udf_split_string](@MYSTR VARCHAR(MAX), @DELIMITER CHAR(1))
RETURNS @MYTBL  TABLE (VALUE varchar(MAX),ID int IDENTITY PRIMARY KEY)
AS 
BEGIN
 <yoastmark class='yoast-text-mark'>DECLARE @RET VARCHAR(MAX)</yoastmark>
 <yoastmark class='yoast-text-mark'>DECLARE @INDEX INT</yoastmark>
 <yoastmark class='yoast-text-mark'>DECLARE @COUNTER smallint</yoastmark>
 
 --Get the first position of delimiter in the main string
 SET @INDEX = CHARINDEX(@DELIMITER,@MYSTR)
 SET @COUNTER = 0
 
 --Loop if delimiter exists in the main string
 WHILE @INDEX > 0
 BEGIN
  --extract the result substring before the delimiter found
  <yoastmark class='yoast-text-mark'>SET @RET = SUBSTRING(@MYSTR,1, @INDEX-1 )</yoastmark>
  <yoastmark class='yoast-text-mark'>--set mainstring right part after the delimiter found</yoastmark>
  <yoastmark class='yoast-text-mark'>SET @MYSTR = SUBSTRING(@MYSTR,@INDEX+1 , LEN(@MYSTR) - @INDEX )</yoastmark>
  --increase the counter
  SET @COUNTER = @COUNTER  + 1 
  --add the result substring to the table
  INSERT INTO @MYTBL (VALUE)
  VALUES (@RET)
 
  --Get the next position of delimiter in the main string
  SET @INDEX = CHARINDEX(@DELIMITER,@MYSTR)
 END
 
 --if no delimiter is found then simply add the mainstring to the table
 IF @INDEX = 0 
 BEGIN
  SET @COUNTER = @COUNTER  + 1
  INSERT INTO @MYTBL (VALUE)
  VALUES (@MYSTR)
 END 
 RETURN   
END

DECLARE @test NVARCHAR(MAX)='Delhi;Maharastra'
SELECT * FROM [dbo].[udf_split_string](@test,';')

It is always advisable to utilize the Normalization techniques while designing a database, which makes the queries perform better. As mentioned above, if you have to deal with a legacy system or a system designed by a developer before you, this would be one of the methods to get your job done.

Vikram T

Backend developer, Works on C#, SQL server lives in Manipal, Karnataka, India.

Also Read:  How to Auto Restart SQL SERVER when its crashed?