I love reading about the normal forms, because it makes me sound like I know what I'm talking about in the conversation where the backend folks tell me, "if we normalized that data then the database would go down". This is usually followed by arguments over UUID versions for some reason.
This was an attempt to extend jokes and not ask for explanation: there are a number of normal forms, and people usually talk about "normalization" without being specific thus conflating all of them; out of 7 UUID versions, only 2 generally make sense for use today depending on whether you need time-incrementing version or not.
Me still using bigints... Which haven't given me any problems. Wouldn't use it for client generated IDs but that is not what most applications require anyway.
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-blessed intermediate points represented by the nominally numbered "forms". I don't find them useful either for thinking about the problem or for communicating about it.
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?")
> 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 basically be merged into BCNF, if I understand correctly, and treated like a general case (as per Darwen).
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.
You can do what you do at the MAC layer without any regard for whether or not it is "OSI layer 2", or whether your MAC layer "cheats" and has features that extend into layers 1, or 3, or any other layer. Failing to implement something useful because "that's not what OSI layer 2 is and this is data layer 2 and the OSI model says not to do that" is silly.
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.
Imperative mood "normalize" assumes that you had something not-normalized before you received that instruction. It's not useful when your table design strategy is already normalization-preservation, such as the most basic textbook strategy (a table per anchor, a column per attribute or 1:N link, a 2-column table per M:N link).
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.
> 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?
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.
I have so many questions about that. Should that normal form basically replace 5NF for the purposes of teaching?
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.
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 :)
This is great. Then I would consider the aggreated, validated, and canonicalized source as a Golden Source. Where I've seen issues is that someone starts to query from a nonauthoritative source because they know about it, instead of going upstream to a proper source.
JSON is extremely fast these days. Gzipped JSON perhaps even more so.
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.
I adore contiguous reads that ideas like that yield. I'd rather push that out to a read-only end point, then getting sucked into the entropy of treating what is effectively an unschema-ed blob into editable data.
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?")
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.
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 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.
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 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 :)
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.