4 ms·
I agree with you on the third normal form, but let's consider the fifth too. In TFA the example is agents working for car makers. Let's use a more realistic e
by jzwinck 12y ago
I agree with you on the third normal form, but let's consider the fifth too. In TFA the example is agents working for car makers. Let's use a more realistic example: agents working for car dealers.
So Frank sells cars for Festive Ford. We store that in fifth normal form by saying that Frank sells cars and Frank works at Festive Ford, and Festive Ford sells cars.
But one day Eve walks in to Festive Ford and walks to trade in her Eagle. Frank cuts her a sweetheart deal and Eve drives away in a brand new Focus. Festive is now left with an Eagle to sell. Maybe we'll add a record saying that Festive sells Eagle cars too, though that seems silly when they've only ever had one to sell, and Eagle is out of business for a decade already. And even if we do that, Frank is a Ford man, so if he manages to sell the Eagle, we'll need to add a record for Frank being an Eagle salesman too.
After a few years of this, everyone is selling every brand according to the database, and the business people wonder why the website says Festival AMC-BMW-Chrysler-Dodge-Eagle-Ford-GMC-Honda-Isuzu-Jeep-Kia-Lexus...oh you silly programmers!
- Arnt 12y agoThat's not what the database says. It doesn't say "x has a current contract with every brand" or "x offers every brand for sale", it says "x has offered every brand at some point in history". I think this is perhaps the biggest problem with 3NF and above: People construct sloppy queries (such as confusing "sells" with "has ever sold in the past"), verify that they give the expected results on a seven-row test table, and then deploy them in production. 3NF and above demand clear thinking.
- davidw 12y agoI am an ardent fan of Postgres, and think it's important to properly store data for all the reasons mentioned elsewhere. That said, there are cases where people get a bit too architecture astronaut with the DB relations, making simple things difficult before those complications are really necessary.
- olavk 12y agoI'm not totally sure what you are getting at, but are you suggesting that the business rules described in the example (that salesmen are limited to selling certain products for certain brands) is not realistic in a real-world car dealership? That might be true, since it is just a simple example intended to demonstrate a concept. If everybody can sell every product, obviously there is no reason to keep a list of who can sell what. You just keep a list of actual sales. But that is a question of designing the database to correspond to the business. It has nothing to do with the merits of any of the normal forms. The normal forms does put any requirements on your business rules.