3 ms·
It was and is still common to have non experts design databases. And in many cases, normal form doesn't make sense. Tables with hundreds or thousands of colu
by wpollock 2y ago
It was and is still common to have non experts design databases. And in many cases, normal form doesn't make sense. Tables with hundreds or thousands of columns are commonly the best solution.
What is rarely stressed about NF is that update logic must exist someplace and if you don't express the update rules in a database schema, it must be in the application code. Subtle errors are more likely in that case.
In the 1990s I was a certified Novell networking instructor. Every month their database mysteriously delist me. The problem was never found but would have been prevented if their db was in normal form. (Instead I was given the VP of education's direct phone number and he kept my records on his desk. As soon as I saw that I was delist, I would call him and he had someone reenter my data.)
Adding fields to tables and trying to update all the application code seems cheaper than redesigning the schema. At first. Later, you just need to normalize tables that have evolved "organically", so teaching the procedures for that is reasonable even in 2024.
- danparsonson 2y ago> The problem was never found but would have been prevented if their db was in normal form. Sorry to be pedantic - if they didn't find the problem, how do we know that was the solution?
- wpollock 2y agoI suppose I don't know for sure. But normal forms prevent false data creation and data loss, and I know their db was not normalized from conversations with their IT support, so it's a pretty good guess.
- deleted 2y ago[deleted]
- petalmind 2y ago> if you don't express the update rules in a database schema, it must be in the application code. Subtle errors are more likely in that case. This is a common sentiment but I think that you can always demonstrate a use case that is very much practical, not convoluted, and could not be handled by classical features of relational databases, CHECK primarily (without arrays of any kinds). A couple of examples is outlined here: https://minimalmodeling.substack.com/i/31184249/more-on-structural-validations https://minimalmodeling.substack.com/i/31184249/more-on-stru... Basically I'm currently pretty much sure that it's impossible to "make invalid states unrepresentable" using a classical relational model. Also, I think that if your error feels subtle then you should elevate the formal model to make it less subtle. You can have "subtle" errors when the checks are encoded as database constraints too.
- wpollock 2y ago>Basically I'm currently pretty much sure that it's impossible to "make invalid states unrepresentable" using a classical relational model. But that's what 9th NF is for! :-)