13 ms·
5NF and Database Design
- tadfisher 6mo agoI 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.
- necovek 6mo agoSo which normal form do they argue for and against? And what UUID version wins the argument?
- Tostino 6mo agoNot OP, but UUID v7 is what you want for most database workloads (other than something like Spanner)
- RedShift1 6mo agoMe 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.
- tossandthrow 6mo agoI use the null uuid as primary key - never had any DB scaling issues.
- tadfisher 6mo agoExplaining jokes is poor form.
- culi 6mo agoOn the internet it is normal.
- necovek 6mo agoThis 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.
- DeathArrow 6mo agoThere are use cases where is better to not normalize the data.
- andrew_lettuce 6mo agoTypically it's better to take normalized data and denormalize for your use case vs. not normalize in the first place. Really depends on your needs
- jghn 6mo agoOver 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
- skeeter2020 6mo ago>> 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 :)
- jghn 6mo agolol took me a second. an amusing autocorrect!
- petalmind 6mo agoOne day I hope to write about denormalization, explained explicitly via JOINs.
- andrii 6mo agoPlease do, you content is great!
- abirch 6mo agoI'm a fan of the sushi principle: raw data is better than cooked data. Each process should take data from a golden source and not a pre-aggregated or overly normalized non-authorative source.
- estetlinus 6mo agoThe 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?
- petalmind 6mo agoFrankly 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/#understanding-the-problem-of-overcounting https://kb.databasedesignbook.com/posts/sql-joins/#understan...
- estetlinus 6mo agoGreat read, thank you!
- mickeyp 6mo agoI'll go one further and say that if you're reaching for DISTINCT and you have joins, you may have joined the data the wrong way. It's not a RULE, but it's ALWAYS a 'smell' when I see a query that uses DISTINCT to shove away duplicate matches. I always add a comment for the exceptions.
- hilariously 6mo agoIt 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.
- estetlinus 6mo agoMakes sense. I’m an OLAP guy.
- hilariously 6mo ago
- jerf 6mo agoIn 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?")
- petalmind 6mo ago> 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). Also, the numeric sequence is not very useful because there are at least four non-numeric forms (https://andreipall.github.io/sql/database-normalization/ https://andreipall.github.io/sql/database-normalization/). Also, personally I think that 6NF should be foundational, but that's a separate matter.
- jerf 6mo ago"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 consultants in the 1980 but I bet even then the real practitioners had a skeptical, arm's length relationship with them.
- carlyai 6mo agolove this
- iFire 6mo agohttps://en.wikipedia.org/wiki/Essential_tuple_normal_form https://en.wikipedia.org/wiki/Essential_tuple_normal_form is cool! Since I had bad memory, I asked the ai to make me a mnemonic: * Every * Table * Needs * Full-keys (in its joins)
- petalmind 6mo agoI 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. I'm super frustrated. This paper is 14 years old.
- iFire 6mo agohttps://dl.acm.org/doi/10.1145/2274576.2274589 https://dl.acm.org/doi/10.1145/2274576.2274589 I'll try reading it again.
- iFire 6mo agoChris Date has a course on this using his parts and supplies example. Don't have time to find it but maybe ai can find it. https://www.oreilly.com/videos/c-j-dates-database/9781449336370/ https://www.oreilly.com/videos/c-j-dates-database/9781449336... https://www.amazon.ca/Database-Design-Relational-Theory-Normal/dp/1484255399 https://www.amazon.ca/Database-Design-Relational-Theory-Norm...
- minkeymaniac 6mo agoNormalize till it hurts, then denormalize till it works!
- Quarrelsome 6mo agowhat a marvelous motto <3. Certainly a lot more concise than the article or the works the article references.
- petalmind 6mo agoImperative 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-preserving, 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. This is my big teaching pet peeve.
- Quarrelsome 6mo ago> 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.
- Quarrelsome 6mo agoEspecially loved the article linked that was dissing down formal definitions of 4NF.
- akdev1l 6mo agoMy brain has been blunted too far due to dynamodb and NoSQL storage usage and now I can’t even normalize anymore
- cremer 6mo agoThe numbered forms are most useful as a teaching device, not an engineering specification. Once you have internalized 2NF and 3NF violations through a few painful bugs, you start spotting partial and transitive dependencies by feel rather than by running through definitions. The forms gave you the vocabulary. The bugs gave you the instinct..
- sgarland 6mo agoNOTE: this is critiquing the author's 4NF definition (from a link in TFA), not TFA itself. > 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 ); If you only ever add an additional supplier or an additional warehouse for a given product, it's only adding one row. But if you add both to the same product, you now have 4 rows for a single product; if you add 5 suppliers and 3 warehouses to the same product, you now have 15 rows for a single product, etc. This fact might be lost on someone if they're creating a table with future expansion in mind without thinking it through, because they'd never hit the cross-product, so the design would seem reasonable. 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.
- petalmind 6mo agoI think I understand this "Cartesian product" reasoning behind 4NF/5NF, I just find it irrelevant I guess. Cartesian product is explained in Kent: case (3) in https://www.bkent.net/Doc/simple5.htm#label4.1 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.
- artyom 6mo agoColor me impressed. Even being very well versed in database design myself, this is just pragmatic and straight to the point, the way I'd have liked it back in the day. 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.
- reval 6mo agoI haven’t finished reading this but I am commenting because of the form. Lead with the conclusions, table of contents, and then sources? This is someone who is confident in what they write. I wish more writing trusted the audience to decide if the writing were important instead of stringing the audience allow. Keep up the good work.
- ibrahimhossain 6mo agoThe article makes a good point about when 5nf becomes impractical. In my experience, stopping at BCNF or 4nf often strikes a better balance unless you have very clear join dependencies. How do others decide where to stop normalizing in real world apps?
- deleted 6mo ago[deleted]
- bvrmn 6mo agoFor me NF>3 seems like an implicit encoding of underlying data logic. They impose additional restrictions (usually contrived and artificial, break really fast in real life) on data not directly expressed as data tuples. Because of that they are hard to explain, natural reaction: "why you just don't store data?".
- umutnaber 6mo agogüzel elinize sağlık
- blueybingo 6mo agothe missing piece in most normalization discussions is the OLAP vs OLTP split. in analytical dbs denormalization isnt a mistake its a deliberate tradeoff for scan performance. teaching normal forms without that context sets people up to make the wrong calls when they hit a warehouse workload
- arh5451 6mo agoi like it but i find the writing style difficult to read.
- petalmind 6mo agoCould you share an example of writing stule that you enjoy?
- mergisi 6mo ago[dead]