The subject of relational and normalized databases has become a recurring theme in our newsletter as more and more of our clients look to store their information in SQL so that they can interact with other systems and make their data available to other areas of the enterprise. And with great normalization comes greater complexity. Creating relationships between tables makes it easier to avoid duplicating data and to maintain data integrity, but it makes it exponentially more difficult to consume, update or delete the data.
Consuming data from normalized databases will inevitably involve your use of JOINs to collect data from multiple tables and reorganize it into the form that the user requires. Updating and deleting data, however, can be a bit more difficult. Due to the relational nature of normalized databases, changes to parent information can have significant, or even catastrophic, effects on the child tables.
Goal of a Normalized Database
When updating just the data associated with your project, then the fact that your database is normalized may help. If you’ve constructed your table structure intelligently, then the data to be updated should only exist in one table. To change the customer contact associated with an order or the expected date to deliver the product, then your effort should just amount to following your relationships from table to table until you get to the single table that holds that data.
However, if you’re looking to update the structure of your data, then things can get more complicated. If your Bill Of Materials (BOM) requires the addition or removal of components, then things do get more complicated. One goal of a normalized databases is to enforce the relationships that exist between the tables. If you delete a record in a parent table (i.e. a table which other tables reference), then those references will be lost, and those child records will be orphaned.
Let’s step away from the theoretical and look at an example. A very common engineering BOM structure might have tables for the products, components, raw materials, and the BOM table itself. Maybe something like this:
The relationships that we have established in this case, indicate that if we were to delete a raw material, ex. a 2”x4”x2’ teak board, then any components that use that raw material, ex. a 16” chair leg, would be orphaned, pointing to a non-existent raw material. Likewise, if we were to delete a product from our product table, ex. the Johnsonville Teak Dining Chair With Arms, then all of the entries in the BOM Components table for that product, ex. the front legs, would be orphaned, i.e. pointing to a broken reference.
In order to prevent this travesty of data suffering, Microsoft SQL Server will most likely prevent you from deleting a record from a parent table that has references in a child table. While that can be helpful in preventing data catastrophes, more often, it’s maddening because you really want to delete that record. In fact, you most likely want to delete all of those records. Once the chair is removed from the products table, there’s no reason to have those BOM entries.