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:
| 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 No. | Customer Name | Customer Address | Customer Phone |
| Â | Â | Â | Â |
| Sales Rep No. | Sales Rep Name | Sales Rep Office |
| Â | Â | Â |
| 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 No | Sales Rep Name | … Â Â Â Â Â | Item Sold |
| 1010 | Jim | Â | Pens |
| Item No. | Item Name | Warehouse | … Â Â Â Â Â |
| 5050 | Pens | Blue | Â |
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 Name | Sales Rep Office | Customer Phone |
| Â | Â | Â | Â |
| Item | Item Name | Warehouse |
| Â | Â | Â |
| 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:
| Sales Rep No. | Sales Rep Name | Sales Rep Address | Item Sold   |
| 1010 | Jim | 100 Brook Way | Pens |
| 1010 | Jim | 100 Brook Way | Computers |
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 Name | Sales Rep Address | Customer Phone |
| Â | Â | Â | Â |
| Item | Item Name | Warehouse |
| Â | Â | Â |
| 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.