5NF and Database Design(kb.databasedesignbook.com) |
5NF and Database Design(kb.databasedesignbook.com) |
> If you read any text that defines 4NF, the first new term you hear is “multivalued dependency”. [Kent 1983] also uses “multivalued facts”. I may be dumb but I only very recently realized that it means just “a list of unique values”. Here it would be even better to say that it’s a list of unique IDs.
This is an inaccurate characterization, and the rest of the post only makes sense when viewed through this strawman. The reason 4NF is explained in the "weird, roundabout way" is because it demonstrates [one of] the precise problem[s] the normal form sets out to solve: a combinatorial explosion of rows.
If you have a table:
CREATE TABLE Product(
product_id INT NOT NULL,
supplier_id INT NOT NULL,
warehouse_id INT NOT NULL
);
The conclusion reached (modulo treating an array as an atomic value) is in fact in 4NF, but it doesn't make any sense why it's needed if you redefine multivalued dependency to mean a set.
Cartesian product is explained in Kent: case (3) in https://www.bkent.net/Doc/simple5.htm#label4.1 ("A "cross-product" form, where for each employee, there must be a record for every possible pairing of one of his skills with one of his languages")
I do not explicitly mention this Cartesian product even tho it is present in both posts ("sports / languages" in 4NF, and "brands / flavours" in 5NF).
> it demonstrates [one of] the precise problem[s] the normal form sets out to solve: a combinatorial explosion of rows.
I just don't understand this wording of "a combinatorial explosion of rows" — what's so dramatic here? I don't need four iterations of algebra-dense papers to explain this concept, I think it's pretty simple frankly.
And my implicit argument is, I guess, exactly that you could design tables that handle both problems without invoking 4NF and 5NF — people are doing that all the time.
Someone, somewhere writing down a list and that list being blessed with the imprimatur of Academic Approval (TM) doesn't mean it is actually useful... sometimes it just means that it made it easy to write multiple choice test questions. (e.g., "What does Layer 2 of the OSI network model represent? A: ... B: ... C: ... D: ..." to which the most appropriate real-world answer is "Who cares?")
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 use your system is going to hate you when they find this table because you didn't think of the situation that they're interested in. "Why does this query use 12 CTEs and random functions I've never heard of and take 5 minutes to return 20,000 rows?" "You broke 1NF."
2NF is usually incorrect to break. Like it's going to be pretty obnoxious to renormalize your data using query logic, but it won't come up nearly as frequently. If it's really never going to come up that often in practical terms, then okay.
3NF and BCNF are nice to maintain, but the number of circumstances where they're just not practical or necessary starts to feel pretty common. Further, the complexity of the query to undo the denormalization will not be as obnoxious as it is for 1NF or 2NF. But if you can do it, you probably should normalize to here.
4NF and higher continue along the same lines, but increasingly gets to what feels like pretty arbitrary requirements or situations where the cost you're paying in indexes is starting to become higher than the relational algebra benefits. 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 represent M:N relationships. If you don't have them, you're either storing multiple values in an array (which, depending on your POV, may or may not violate 1NF), or you have a denormalized wide table with multiple attributes, some of which are almost certainly NULL.
Also, these all serve to prevent various forms of data anomalies. Databases must be correct above all else; if they're fast but wrong, they're useless.
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 basically be merged into BCNF, if I understand correctly, and treated like a general case (as per Darwen).
Also, the numeric sequence is not very useful because there are at least four non-numeric forms (https://andreipall.github.io/sql/database-normalization/).
Also, personally I think that 6NF should be foundational, but that's a separate matter.
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 consultants in the 1980 but I bet even then the real practitioners had a skeptical, arm's length relationship with them.
I share your ideal, but there exists a slight problem: no RDBMS I'm aware of really facilitates 6NF or DKNF (or even Codd's full relational concept; or newfound essentials like relational-division, and so on...).
There are also genuine ergonomic issues to contend with: pretty-much every RDBMS design and/or administration tool I've used in the past 20 years (SSMS, SSDT, DBeaver, MSAccess (lol), phpMyAdmin, etc) will present the database as a long, flat list of tables - often only in alphabetical order (if you're lucky, the tooling might let you group the tables into logical subfolders based on some kind of 2-part name scheme baked into the RDBMS (e.g. "schemas" in MSSQL).
...which starts being counterproductive when 6NF means you have a large number of tables that absolutely need to exist - but aren't really that significant alone by themselves; but they always need to remain accessible to the user of the tool (so they can't be completely hidden). So you'll turn to the Diagramming feature in your DB GUI, which gives you a broader 2D view of your DB where you can proximally group related objects together - instead of endlessly scrolling a long alphabetical list; and you can actually see FKs represented by physical connections which aids intuitive groking when you're mentally onboarding onto a huge, legacy production DB design.
...but DB diagrams are just too slow to load (as the tooling needs to read the entire DB's schema, design; all objects first before it can give you a useful view of everything - it's just so incredibly grating; whereas that alphabetical list loads instantly.
Sorry I'm just rambling now but anyway, my point is, 6NF is great, but our tooling sucks, and the RDBMS they connect to suck even more (e.g. SQL-92 defined the 4 main CONSTRAINT types seen in practically all RDBMS today (CHECK, FOREIGN KEY, UNIQUE, and DEFAULT); over 30 years later we still have the same anaemic set of primitive constraints; only Postgres went further (with its `EXCEPT` constraint). As of 2026, and almost 40 years since it was defined, no RDBMS supports ASSERTION constraints; wither DOMAIN constraints and a unified type-system that elegantly mediates between named scalars, relations (unordered sets of tuples), queries, and DOMAINs and the rest.
...this situation is maddening to me because so many data-modelling problems exist _because_ of how unevolved our RDBMS are.
Even when it's purely performance-related, it usually gets a shrug, and "it's good enough." Cool, you're wrecking the B+tree, maybe don't do that. It's as if I said, "I'm using an array to store these millions of items that I later need to de-duplicate," and when someone suggests maybe using a set, I dismiss it.
To stay on the main topic, same for the "normalization forms". Do what your database needs.
The concepts are just attractive nuisances. They are more likely to hurt someone than to help them.
Certainly a lot more concise than the article or the works the article references.
And this is basically the main point of my critique of 4NF and 5NF. They both traditionally present an unexplained table that is supposed to be normalized. But it's not clear where does this original structure come from. Why are its own authors not aware about the (arguably, quite simple) concept of normalization?
It's like saying that to in order to implement an algorithm you have to remove bugs from its original implementation — where does this implementation come from?
The other side of this coin is that lots of real-world design have a lot of denormalized representations that are often reasonably-well engineered.
Because of that if you, as a novice, look at a typical production schema, and you have this "thou shalt normalize" instruction, you'll be confused.
This is my big teaching pet peeve.
I would maybe throw in date as an key too. Bad idea?
I tried to explain the real cause of overcounting in my "Modern Guide to SQL JOINs":
https://kb.databasedesignbook.com/posts/sql-joins/#understan...
Since I had bad memory, I asked the ai to make me a mnemonic:
* Every
* Table
* Needs
* Full-keys (in its joins)
Why do they hate us and do not provide any illustrative real-life example without using algebraic notation? Is it even possible?
I just want to see a CREATE TABLE statement, and some illustrative SELECT statements. The standard examples always give just the dataset, but dataset examples are often ambiguous.
> (in its joins)
Do you understand what are "its" joins? What is even "it" here.
I'm super frustrated. This paper is 14 years old.
I'll try reading it again.
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.
Each process should take data from a golden source and not a pre-aggregated or overly normalized non-authorative source.
I find that JSON blobs up to about 1 megabyte are very reasonable in most scenarios. You are looking at maybe a millisecond of latency overhead in exchange for much denser I/O for complex objects. If the system is very write-intensive, I would cap the blobs around 10-100kb.
Considering the data transfer alone for 1 MB / 1 msec requires 8 Gbps, I have doubts. But for fun, I created a small table in Postgres 18 with an INT PK, and a few thousand JSONB blobs of various sizes, up to 1 MiB. Median timing was 4.7 msec for a simple point select, compared to 0.1 msec (blobs of 3 KiB), and 0.8 msec (blobs of 64 KiB). This was on a MBP M4 Pro, using Python with psycopg, so latency is quite low.
The TOAST/de-TOAST overhead is going to kill you for any blobs > 2 KiB (by default, adjustable). And for larger blobs, especially in cloud solutions where the disk is almost always attached over a network, the sheer number of pages you have to fetch (a 1 MiB blob will nominally consume 128 pages, modulo compression, row overhead, etc.) will add significant latency. All of this will also add pressure to actually useful pages that may be cached, so queries to more reasonable tables will be impacted as well.
RDBMS should not be used to store blobs; it's not a filesystem.
https://www.oreilly.com/videos/c-j-dates-database/9781449336...
https://www.amazon.ca/Database-Design-Relational-Theory-Norm...
Real-talk: those consultants are absolutely essential - and are the unsung heroes of so many "organic" database projects that would have gotten started as an Excel spreadsheet on a nontechnical middle-manager's workgroup-networked desktop, which grew over time into a dBase file, then MSAccess/JET, then MSDE or MSSQL Express if they (think) they knew what they're doing, and then if it's the mid-2000s then maybe it'll be moved onto dedicated on-prem Oracle or MSSQL box - but still an RDBMS; I remember in 2014 all the talk was about moving data out of on-prem RDBMS siloes and onto Cloud(TM)-y OLAP clusters (trying to hide the fact they're running stock Postgres) which acted as a source for a Hadoop cluster - all to produce dashboards and visualizations made with the $100k Tableau license your company purchased after their sales guys showed your org's procurement people a good time in Cancun.
None of the evolution and progress described above could have happened if not for the awful DB designs in that initial Access DB - the anti-patterns would be carried through the DB whenever it ascended to the next tier of serious-business-ness, and each and every design-decision made out of innocent ignorance gets gradually massaged-out of the model by the regular and recurring visits by DBA consultants - because (and goddamnit it's true): a depressingly tiny proportion of software people (let alone computer-people) know anything about DB design+theory - nor all the vendor-specific gotchas.
What I still don't understand is how in 2026 - after 30 years of scolding beginners online - that we've successfully gotten greenhorn software-dev people to move away from VBA/VB6's dead-end, PHP's unintentional fractal of bad design, and MySQL's meh-ness - and onto sane and capable platforms like TypeScript, Node, and Postgres - all good stuff; and yet on my home-turf on StackOverflow, I still see people writing SQL-92 style JOINs and CREATE TABLE statements covered in more backticks than my late grandmother's labrador. I honestly have no idea where/when/how all those people somehow learned SQL-92's obsolete JOIN syntax today.
So in conclusion: the evidence suggests that not enough people today truly understand databases well-enough to render expensive DBA consultants irrelevant.
for immutable blobs use crypto digest as db key and store blobs in file system.
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 :)
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?
I've never had to do 18 joins to extract information in my career. I'm sure these cases do legitimately exist but they are of course rare, even in large enterprises. Most companies are more than capable of distinguishing OLTP from OLAP and real-time from batch and design (or redesign) accordingly.
Databases and their designs shift with the use case.
Really? You're not representing particularly complex entities with your data.
I work on a student information system. 18 joins isn't even weird. If I want a list of the active students, the building they're in, and their current grade level, that's a join of 8 tables right there. If I also want their class list, that's an additional 5 or 6. If you also want the primary teacher, add another 4. If you want secondary staff, that's another 5.
The whole system is only around 500 GB, but it's close to 2,000 tables. Part of the reason is tech debt archaic design from the vendor, but that's just as likely to reduce the number of tables as it is to increase them. The system uses a monolithic lookup table design, and some of the tables have over 300 columns. If they were to actually properly normalize the entire system to 3NF, I have no doubt that it would be in the hundreds of thousands of tables.
I may be misunderstanding you, but to me it sounds like you're conflating domain modeling with schema modeling. If your domain is like most SaaS apps, then Phone, Email, Address, etc. are probably all attributes of a User, and are 1:N. The fact that multiple Users may share an Address (either from multiple people living together, or people moving) doesn't inherently mean you have an M:N relationship that you must model with schema. If you were using one of those attributes as an identity (e.g. looking up a customer by their phone number), that still doesn't automatically mean you have to model everything as M:N - you could choose to accept the possibility of duplicates that you have to deal with in application code or by a human, or you could choose to create a UNIQUE constraint that makes sense for 99% of your users (e.g. `(phone_number, deactivated_at)` enforces that a phone number is only assigned to one active user at a time), and find another way to handle the rare exceptions. In both cases, you're modeling the schema after your business logic, which is IMO the correct way to do so.
I apologize if I came across as implying that any possible edge case means that you must change your schema to handle it. That is not my design philosophy. The schema model should rigidly enforce your domain model, and if your domain model says that a User has 0+ PhoneNumber, then you should design for 1:N.
> 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.
If the attribute is mostly 1:1, then whether or not you should decompose it largely comes down to semantic clarity, performance, and the possibility of expansion.
This table is in 3NF (and BCNF, and 4NF):
CREATE TABLE User (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(254) NOT NULL,
phone VARCHAR(32) NULL
);
CREATE TABLE User (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(254) NOT NULL,
phone_1 VARCHAR(32) NULL,
phone_2 VARCHAR(32) NULL,
);
CREATE TABLE User (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(254) NOT NULL,
phone_1 VARCHAR(32) NULL,
phone_1_type ENUM('HOME', 'CELL', 'WORK') NOT NULL,
phone_2 VARCHAR(32) NULL,
phone_2_type ENUM('HOME', 'CELL', 'WORK') NOT NULL,
);
> 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?
The only times I've seen anything close to that many joins are:
1. Recreating a denormalized table from disparate sources (which are themselves often not well-constructed) to demonstrate that it's possible. 2. Doing some kinds of queries in MySQL <= 5.7 on tables modeling hierarchical data using an adjacency list, because it doesn't have CTEs. 3. When product says "what if we now supported <wildly different feature from anything currently offered>" and the schema was in no way designed to support that.
Even with the last one, I think the most I saw was 12, which was serendipitous because it's the default `geqo_threshold` for Postgres.
I find the bafflement expressed in the article as well as the one linked extremely attractive. It made both a joy to read.
Were I to hazard a guess: Might it be a consequence of lack of disk space in those early decades, resulting into developers being cautious about defining new tables and failing to rationalise that the duplication in their tragic designs would result in more space wasted?
> The other side of this coin is that lots of real-world design have a lot of denormalized representations that are often reasonably-well engineered.
Agreed, but as the OP comment stated they usually started out normalised and then pushed out denormalised representations for nice contiguous reads.
As a victim of maintaining a stack on top of an EAV schema once upon a time, I have great appreciation for contiguous reads.
A plausible explanation of "normalization as a process" was actually found in https://www.cargocultcode.com/normalization-is-not-a-process... ("So where did it begin?").
I hope someday to find some technical report of migrating to the relational database, from around that time.
That makes much more sense as reasoning.
If I can also offer a second hazard of guess. I used to work in embedded in the 2000's and it was absolutely insane how almost all of the eldy architects and developers would readily accept some fixed width file format for data storage over a sensible solution that offered out of the box transactionality and relational modelling like Sqlite. This creates a mindset where each datastore is effectively siloed and must contain all the information to perform the operation, potentially leading to these denormalised designs.
Bit weird, given that was from the waterfall era, implying that the "Big Design Up Front" wasn't actually doing any real thinking about modelling up front. But I've been in that room and I think a lot of it was cargo cult. To deal with the insanity of simple file I/O as data, I had to write a rudimentary atomicity system from scratch in order to fix the dumb corruption issues of their design when I would have got that for free with Sqlite.
What would your ideal RDBMS / tooling look like, that facilitates 6nf effectively? Do you think it's more a limitation of the query/storage engine, or the query language (SQL), or the user interface? Do you think founding on Datalog (or similar), which kinda lends itself to "narrow" relations, instead of SQL which kinda lends itself to "wide" relations, would help here?
(I ask as one of my personal hobby-horses is trying to design better query languages and tooling, and 6nf/datalog maintains a kinda special place in my heart)