Part 5 of 6 · 2 min read
One tiny comparison, start to finish
The bookstore's address problem, solved, with one number showing the saving.
One tiny comparison, start to finish
This part walks the whole idea once, start to finish, on the smallest possible version of the problem from part 1.
Step 1: the plain-file version. A customer places 3 orders over a year, then moves house. Their address is stored 4 times: once in customers.txt, once inside each of the 3 order records. Every one of the 4 has to be found and corrected by hand or by a script.
Step 2: the database version. The same customer's address is stored once, in one row of customers. The 3 orders each hold only customer_id = 107, never an address.
Step 3: do the number. Across all 400 customers, assume each has placed 3 orders on average — 1,200 orders, matching the table above. The plain-file version stores the address 1,200 + 400 = 1,600 times in total. The database version stores it 400 times — once per customer, no matter how many orders they place. That is a 75 percent reduction in copies of the one fact that actually changes when someone moves.
Step 4: name what this bought. Fewer copies means fewer chances to update only some of them. The reduction in part 3 is not the main point — it is evidence of the real one: with one copy of the address, there is no version of "this order shows the old address" left to happen at all.
Remember this
A database management system's first job is removing unnecessary copies of the same fact, because every copy is a place an update can be missed. Everything else this subject covers builds on top of that one idea.
Quick notes
- Plain files let the same fact get copied in more than one place; nothing enforces that the copies agree.
- A DBMS stores a fact once, and makes every program go through it, so it can enforce rules no single program could enforce alone.
- A DBMS gives an application four things plain files do not guarantee: no needless redundancy, enforced integrity, safe concurrent access, and durability after a crash.
- This subject's running example is a small bookstore (
customers,orders, and more tables added in later levels), used so examples build on each other instead of starting over. - A reference such as
customer_idinsideordersreplaces a copied address with a pointer to the one row that actually holds it.