This helps improve the performance of a SQL engine when locating and loading those data pages off the disk, which is typically the biggest bottleneck in a server's provisioned hardware. So overall your database system and the applications that consume it will be able to run more efficiently.Īnother reason performance may improve due to normalization is because your tables (and more so the objects they live in on the disk - generally referred to as data pages) become smaller in data size.
#Database normalization update#
Less rows to update generally means shorter lock times and less chance of lock escalation (when applicable). Now if the description of an Item needs to change, you'd only have to update it in one place, that single row in the Items table.įor similar reasons this would improve performance, by minimizing the amount of work the above type of maintenance requires. The Customer_Sale table would no longer have a column called ItemDesc (you'd be able to reference it in the Items table by joining on the ItemNo field in both tables). So there would only be one record for ItemNo = 123 with the ItemDesc column in the Items table. The primary key of that table would likely be the ItemNo (assuming that's the unique identifier for an Item here). Normalization would be to have another table called Items which would store one row per unique Item and is where the ItemDesc would live.
With the current denormalized design of storing the ItemDesc in the Customer_Sale table, you would need to update all 10s of thousands of records to fix the ItemDesc. And then the business realizes the ItemDesc is wrong and needs to be updated. Imagine the scenario where ItemNo = 123 has been sold to Customers for the past year, 10s of thousands of sales for example. As you see in your example table Customer_Sale, things that aren't central to a Sale are repeated unnecessarily.Ī good example of this is the ItemDesc column. Reduces data redundancy which improves maintainability. In short, database normalization employs the following benefits: Here's a Microsoft article that does a pretty good job explaining normalization and the different forms, albeit a little dated regarding Microsoft Access, the theory and principles still apply.
0 kommentar(er)
