Database Normalization in Dynamics NAV

Database Normalization in Dynamics NAV
 

Anomalies and Database Normalization

The three most common types of database anomalies include insertion, deletion, and update. These anomalies come from poor database design and can cause errors and other frustrations. This article will explain all three of these common database irregularities and provide solutions. For this article to provide real value, a basic understanding of database management is assumed. For a general overview – or just a quick a refresher – of database normalization please refer to this earlier ArcherPoint blog, Understanding Database Management in NAV.

Insertion Anomalies

Insertion anomalies occur when trying to insert a new record into the database. For example:
Sample Table
Customer No.Customer Name...                      Sales Rep Assigned
    
In this case every sales representative is assigned a customer and is responsible for selling and maintaining a relationship with that given customer. If a new record is inserted in this database, a sales rep must be assigned at the same time the customer is being inserted. Logically it doesn’t make sense that as soon as a customer is added to the database, a sales rep needs to be assigned as well. This is bad design. Fortunately, there is a more effective way to do this. The more logical configuration:
Customer Table
Customer No.Customer NameCustomer AddressCustomer Phone
    
 
Sales Representative Table
Sales Rep No.Sales Rep NameSales Rep Office
   
 
Connecting Table
Customer No.Sales Rep No.
  
  Having separate tables for the customer and the sales rep, as well as one that connects the two, allows for fewer anomalies. This way, a sales rep does not need to be automatically, and perhaps prematurely, assigned to a customer. In another example, imagine a database at a school. There are classes and teachers and similarly, the first design doesn’t allow for a class to be inserted without a teacher being assigned to teach the class. This again is an issue since classes can exist independently of teachers and sometimes there may not be a teacher available to teach a class. Again, there is a simple fix for this problem. Use a structure similar to the trio of tables above: a table for classes, a table for teachers, and one table to link the two.

Deletion Anomalies

Deletion anomalies occur when trying to delete an existing record from the database. For example:
Sales Rep NoSales Rep Name...         Item Sold
1010Jim Pens
 
Item No.Item NameWarehouse...          
5050PensBlue 
Assume the company stops selling pens. If this record is deleted from the Item table, then the Sales Rep table will lose the corresponding Item Sold field information. This will cause cascading errors in all the Sales Rep table records that were selling Pens. This is not desirable and can be easily avoided through normalization. Solution:
Sales Rep No.Sales Rep NameSales Rep OfficeCustomer Phone
    
 
ItemItem NameWarehouse
   
 
Sales Rep No.Item            
  
By formatting tables in the above manner, assigning an Item Sold to each Sales Rep is no longer necessary. Also if an item is deleted, the Sales Rep table will not be impacted. Additionally, this could be solved by having the database put a null value in place of the Item Sold, however this is not ideal. By normalizing and creating a linking table, this keeps the two original tables from being impacted by any cascading errors that may occur from the deletion.

Update Anomalies

Update anomalies occur when trying to update an existing record from the database. For example:
Update Anomalies Example
Sales Rep No.Sales Rep NameSales Rep AddressItem Sold    
1010Jim100 Brook WayPens
1010Jim 100 Brook WayComputers
Solution:Two records are necessary here to show that Jim sells two different items. Let’s say that Jim moves. It is very easy to update one record and forget the other. Resulting in one record with the old address, and one with the new address. This can cause all sorts of real world problems, such as mail being sent to the wrong address or both addresses. These errors can be avoided once again by splitting the tables.
Sales Rep No.Sales Rep NameSales Rep AddressCustomer Phone
    
 
ItemItem NameWarehouse
   
 
Sales Rep No.Item Sold
  
Splitting the tables allows updates to occur in one place without negatively impacting other tables. While experiencing database anomalies can be frustrating, fixing them does not have to be. By keeping tables flexible and ensuring the database structure is sound will insure efficiency overtime and help prevent puzzling errors to users in the future.   For more ideas and information about optimizing your databases in NAV, please contact our experts at ArcherPoint and be sure to visit our vast resource center online.

Trending Posts

Stay Informed

Choose Your Preferences
First Name
*required
Last Name
*required
Email
*required
Subscription Options
Your Privacy is Guaranteed