51–60 of 86 posts
Earlier quoted context omitted.
Over time I’ve developed a philosophy of starting roughly around 3NF and adjusting as the project evolves. Usually this means some parts of the db get demoralize and some get further normalized
>> Usually this means some parts of the db get demoralize I largely agree with your practical approach, but try and keep the data excited about the process, sell the "new use cases for the same data!" angle :)
Normalize till it hurts, then denormalize till it works!
I think the main problem of how 4NF and 5NF formal definitions were taught is that essentially common sense (which is mostly "sufficient" to understand 1NF-3NF) starts to slip away, and you start needing the mathematical background that Ed Codd (and others) had. And trying to avoid that is how those weird examples came up.
Earlier quoted context omitted.
I still see value in the numbering. Breaking 1NF is essentially always incorrect. You're fundamentally limiting your system, and making it so that you will struggle to perform certain queries. Only break 1NF when you're absolutely 100% certain that nobody anywhere will ever need to do anything even slightly complex with the data you're looking at. And then, probably still apply 1NF anyways. Everyone that ever has to…
> Your database disk usage by table report is going to be dominated by junction tables, foreign key constraints, and indexes, and all you're really buying with that disk space is academic satisfaction. FK constraints add a negligible amount of space, if any. The indexes they require do, certainly, but presumably you're already doing joins on those FKs, so they should already be indexed. Junction tables are how you re…
Yeah, the problem is that when you get to 4NF+, you're often looking at creating a new table joining through a junction table for a single multi-valued data field that may be single values a plurality or majority of the time. So you need the base table, the junction table that has at least two columns, and the actual data table.
So, you've added two tables, two foreign key constraints, two primary key indexes, potentially more non-clustered indexes... and any query means you need two joins. And data validation is hard because you need to use an anti-join to find missing data.
Or, you can go with an 1:N relationship. Now you have only one more table at the cost of potentially duplicating values between entities. But if we're talking about, say, telephone numbers? Sure, different entities might share the same phone number. Do you need a junction table so you don't duplicate a phone number? You're certainly not saving disk space or improving performance by doing that unless there's regularly dozens of individual records associated to a single phone number.
And if the field is 1:1... or even 90% or 95% 1:1... do you really need a separate table just so you don't store a NULL in a column? You're not going to be eliminating nulls from your queries. They'll be full of LEFT JOINs everywhere; three-valued logic isn't going anywhere.
> Databases must be correct above all else; if they're fast but wrong, they're useless.
Yeah, and if they're "correct" but you can't get it to return data in a timely manner, they're also useless. A database that's a black hole is not an improvement. If it takes 20 joins just to return basic information, you're going to run into performance problems as well as usability problems. If 18 of those joins are to describe fidelity that you don't even need?
In a roundabout way this article captures well why I don't really like thinking in terms of "normal forms", especially as a numbered list like that. The key insights are really 1. Avoid redundancy and 2. This may involve synthesizing relationships that don't immediately obviously exist from a human perspective. Both of those can be expanded on at quite some length, but I never found much value in the supposedly-bless…
Earlier quoted context omitted.
> Someone, somewhere writing down a list and that list being blessed with the imprimatur of Academic Approval (TM) One problem is that normal forms are underspecified even by the academy. E.g., Millist W. Vincent "A corrected 5NF definition for relational database design" (1997) (!) shows that the traditional definition of 5NF was deficient. 5NF was introduced in 1979 (I was one year old then). 2NF and 3NF should bas…
"1979 (I was one year old then)." Well, we are roughly the same age then. Our is a cynical generation. "One problem is that normal forms are underspecified even by the academy." The cynic in me would say they were doing their job by the example I gave, which is just to provide easy test answers, after which there wasn't much reason to iterate on them. I imagine waiving around normalization forms was a good gig for co…
The lost art of normalizing databases. ”Why is the ARR so high on client X? Oh, we’re counting it 11 times lol”. I would maybe throw in date as an key too. Bad idea?
It depends on if you are doing OLTP (granular, transactional) vs OLAP (fact/date based aggregates) - dates are generally not something you'd consider in a fully normalized flow to uniqify records.
The lost art of normalizing databases. ”Why is the ARR so high on client X? Oh, we’re counting it 11 times lol”. I would maybe throw in date as an key too. Bad idea?
Frankly I don't think that overcounting is solved by normalizing, because it's easy to write an overcounting SQL query over perfectly normalized data. I tried to explain the real cause of overcounting in my "Modern Guide to SQL JOINs": https://kb.databasedesignbook.com/posts/sql-joins/#understan...