7 ms·
Historically, 4NF explanations are needlessly confusing
- mgkimsal 2y ago"The existence of the theory implies that the “composed” design somehow arises, naively(?) or naturally." Given how many first timers come up with really weird schema designs, I'm not necessarily surprised, although I agree presenting it as perhaps a default-but-wrong approach doesn't help much.
- anonzzzies 2y agoNot only beginners. I work with large teams with almost only seniors (10+ years of programming job experience) for large corps who don't know what 4nf is; they just chuck stuff in a table, update the same data across multiple tables, don't use indices or cascading rules etc. Everything is in code; the db just stores it, badly. They threat everything like it's mongo and this doesn't even work in mongo (no indices and it dies as well of course). 4NF was explained confusingly because it's quite complex theory. The fact the author says 'to be best of his understanding' it is 4nf is kind of telling. With many things in software dev; it all doesn't matter until it matter. With few records and few users, like the average department LoB crud thing, it's not really a problem; it will be when either or both grow. My 'colleagues' (I am external) claim to know all this stuff, but in reality there is no proof they do; I have never seen them do things correctly with respect to dbs or software in general. That makes me a lot of money, but it is depressing really.
- petalmind 2y ago> 4NF was explained confusingly because it's quite complex theory. The fact the author says 'to be best of his understanding' it is 4nf is kind of telling. > My 'colleagues' (I am external) claim to know all this stuff, but in reality there is no proof they do; I have never seen them do things correctly with respect to dbs or software in general. Wait I don't get it. Are you saying that "(instructor_id, skill_id); PK (instructor_id, skill_id)" (and the same for languages) is NOT in 4NF; and I am not right? What is in 4NF, then?
- anonzzzies 2y agoI think you misunderstood me? I am talking about my colleagues... Or the many different colleagues I have throughout the year.
- petalmind 2y agoAh, okay, yes, sorry, thank you!
- mgkimsal 2y agoI was initially going to add some stuff I find a bit... shocking in the 2020s related to what I think are basic database stuff (maybe not specifically 4nf related). Held off, but I'll add one. A colleague had to argue that an 'address' table should have a synthetic primary key, instead of a natural key. The argument for the initial 'design' held that 'the address itself is unique - there's not 2 '123 west main streets'!". Wasn't my company directly, but the crux of the conversation was shared with me, and ... in 2022, with however many articles, tutorials, books, classes, stackoverflow, etc... having to argue that 'no, someone's entire address should not be the natural key for a table' was ... flummoxing to me.
- jcrawfordor 2y agoI think the typical presentation of 4NF makes more sense when you consider the ergonomics of data processing systems when relational modeling emerged. Predating computing, data processing was all centered around "records." Relational databases are often also described in terms of "records," relational modeling emerged out of an attempt to formalize data processing practices, the practitioners of the era were learning relational modeling from a data processing background, so it's no surprise that relational concepts are often presented "as opposed to" the way things were done in data processing. In traditional data processing systems, a "record" was far more independent than we think of in a relational database. Records were hand-punched onto cards or keyed onto magnetic tape to be sorted, summarized, or whatever operation you cared about by a data processing machine. In this environment, joins were extremely expensive operations, often requiring that the operator feed the card stack over and over again (a rather literal O(n^2)). So, the "weird composed" schema is absolutely what you would do. And a lot of computer software was built around the exact same concepts, which made sense anyway as computers often continued to use sequential-access storage devices with similar (but less severe) problems around joins. This era famously persisted for a long time, with relational modeling as an academic concept well predating successful implementations of relational databases. One could argue that all of the normal forms are pretty much "stop doing it the old sequential access (card-stack) way and use random access concepts (keys and joins) instead." Of course that leaves the question of whether or not we should teach it that way... we don't tend to tell students about how memory is now random access, so perhaps by the same turn the historical approach isn't useful for teaching here. But it would undoubtedly be more effective if you give a little of the history.
- pessimizer 2y agoWhat might be needless is the "4" in "4NF," because the first three (and a half) are obsolete. Or rather, as you mention, were only relevant when things were read sequentially. "Normalization" is really fine on its own. Things that aren't normalized are "ad hoc," or really just "input."
- cma 2y agoSequential is still faster in a lot of cases even on SSDs and RAM (prefetching)
- emmelaich 2y agoExcellent article. 1NF is part of the problem as the article mentions. I'm seen some articles say that atomic in 1NF means you cannot have things that can be decomposed. All that matters is that you can perform the relational algebra on the table and get a sensible result. To the why of presenting normalisation from those wacky forms, I think that also comes from data ingestion. You'll get a list of attributes for an employee from their CV and might naively just load that 'as is' into a table.
- epcoa 2y ago> you cannot have things that can be decomposed. Indeed. You couldn’t store integers because those are decomposable into bits. Atomicity was always a BS concept, and Chris Date and Hugh Darwen themselves have written as much on this topic. It didn’t help that although Edgar Codd had a math background, a lot of things published on the relational model just didn’t have formal rigor, but people interpreted it as such.
- Ygg2 2y agoWell without atomicity you can't have 1NF, and thus none of the latter. So let's hope you never store a content of a text content of a configuration or log.
- petalmind 2y agoI don't understand that comment. What do you mean "let's hope" if I can easily store a text content of configuration or log into a column?
- emmelaich 2y agoI believe it's a joke. However ... if you store a CV as a blob then separately store skills, experiences in a different table --- does that make the db design imperfect?
- 2y ago
- lmm 2y agoA possible clue is that the weird composed form is, still, the only possible form in which you can actually get results out of your SQL database. Perhaps people worked backwards from sample query results, reasoning that an output that looks like that should come from a table that looks like that. Of course that begs the question of why that ever seemed like a sensible format for output, but apparently the SQL establishment has never seen fit to change it, so either the SQL people are all idiots or there's some advantage to that form. (Yes, I know there is an obsolete meaning of that phrase still recorded in some dictionaries, but I prefer to use live English)
- sopooneo 2y agoI was about to say I thought you might be mistaken because you can always use some variant of "group concat" to get sets of fields to show in comma seperated form... But then I realized even with that, I couldn't get the output under discussion without subqueries. And I don't consider that a win for me.
- wpollock 2y agoIt 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.
- Kerrick 2y agoI’ve seen this design again and again, but not in relational databases. I usually see it in spreadsheets. I would be completely unsurprised to see a three column Excel sheet with Employee, Skill, and Language headers. (https://kerricklong.com/tmp/demo-4nf-need.jpg https://kerricklong.com/tmp/demo-4nf-need.jpg) The spreadsheet designer’s intuition is that Skill and Language are lists, and Employee is a section header. Smith might have a few rows with his languages and skills listed, one per cell, in arbitrary top-to-bottom order and with no consideration for how language and skill in a row relate to each other—only to Smith. The Employee column has a single merged cell for Smith, a number of rows tall it needs to be to contain the largest of his lists. When migrating this from a spreadsheet to a database, you lose merged cells as a feature and can no longer rely on order—so you copy the value Smith to each cell that had been merged.
- petalmind 2y agoThis is a very good graphical demonstration (especially the merged cells), thank you. It is also a variation "2b" from Kent's list (just for reference). I think that the textual demonstration of the same effect could be found in https://www.cargocultcode.com/normalization-is-not-a-process/ https://www.cargocultcode.com/normalization-is-not-a-process... ("So where did it begin?" section). I rediscovered this blog post after I published the article.
- thwarted 2y agoThe origin of classic explanation probably comes from people attempting to computerize paper forms. The school would have profiles for each teacher, probably a series of pages in a manila folder stored in a filing cabinet, that was filled out by hand/a typewriter. A space for the name. A series of blanks to be filled in with a list of skills (that maxes out at however many blanks they could put on the page). A series of blanks for languages. Woe is the teacher who knows more languages than the available blanks that are on the form! People still create tables today (yes, even in 2024) like (teacher, skill, language) as the row definition. Someone looks at the list of information they are collecting, conclude that the only axis they need to query on is the teacher, and make wide tables with many columns with disjoint purposes, that are then difficult to query and are inflexible. Consider a library card catalog and the Dewey Decimal Classification system. The cards are arranged in the drawers by the topics standardized in the DDC. It looks like the major axis is the DDC numbering and their associated topics. While a card catalog lets you search for specific books if you know the topic and the author, it can not easily tell you all the books by the same author, or the list of topics that an author covers. Or find all the books that multiple authors have written together. But if one was to take the card catalog as it existed and computerize it, the naïve implementation would look like the unnormalized or lower normalization forms. The explanation of the progression of increasing normalization tells you how to incrementally achieve normalization given a data schema that was heavily influenced by the limitations of physical reality, such that a card catalog is, into a system that does not have those constraints. The example of storing the skills and language as a list in a column is grossly inefficient in usage (ignoring the inefficiencies in storage and performance) and ignores that the "list" in 4NF is actually the result set, the set of rows returned. I suppose it could help one to think of the result set as horizontal columns (a list) rather than a vertical set of rows, but that's more of a side effect of the data presentation than the relational algebra underpinnings. Despite that databases like postgres let you query and index on expressions applied to a JSON-typed column, you end up with something more complex, with different methods of enforcing data hygiene at the row and the individual column level, because you've got sets of data in a single column, when the set is the defined by the rows. 4NF lets you answer way more questions about the data than you might initially anticipate. I've worked with a number of schemas that someone else created with no or little normalization that literally could not answer the desired questions. In this example, improper normalization results in finding out the skills of a teacher is easy, but finding out if the school is weak in any areas and what they should hire for is hard. But when the data is 4NF, you can answer questions easily in both directions without jumping through any data conversion hoops and you can have high confidence that the data remains hygienic.
- westurner 2y agoDatabase normalization: https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization 4NF: https://en.wikipedia.org/wiki/Fourth_normal_form https://en.wikipedia.org/wiki/Fourth_normal_form
- lowbloodsugar 2y agoWhen I learned quantum mechanics as part of a Chemistry BSc, it was broken down so as to be easier to digest. It made it much fucking harder for me. When I did it as a Phsyicist, they just laid out the math and it was super straightforward. I think back when they came up with 4NF, everyone was still trying to figure out what the fuck they were talking about, and so explanations were needlessly complicated. Anyway, now with non-relational databases, 4NF can slow you down.
- Dylan16807 2y agoWhat if the languages available do depend on what the lesson is about? That would require the composed form. Skipping it entirely is not the way to go. You could start with decomposed and then compose it, I guess. I don't think either order is inherently easier to learn. I agree that the wording of the normal forms, before getting to examples, is usually confusing.
- petalmind 2y ago> What if the languages available do depend on what the lesson is about? Then it would be a completely different schema that would just be in 3NF. If you look at Kent's variations you will see that they work hard (graphically) at making sure that you do not assume dependency between language and lesson.
- Dylan16807 2y agoThe point is, the schema is not inherently unreasonable as the article is implying. There's a reason to make the comparison and take both versions seriously, not just show off the normalized version.
- petalmind 2y agoYes, absolutely, just present it in "historical misunderstandings" section. My only concern is that it is presented as if it was something natural to begin with.
- jklowden 2y agoPerhaps TFA could explain what happened to math between 1977 and 2024. Because the meaning of "atomic" didn’t change, nor did the meaning of 1NF. The author pretends to explain 4NF, but never actually begins. He calls himself an "historian", which he might be. But he betrays very little knowledge of the relational model. Repeating fields, which he proclaims as some kind of innovation, violate 1NF. They’re not atomic. Maybe the easiest way to understand it is that the list of languages can’t be used in SQL. If one teacher speaks Italian and French, SQL won’t find WHERE LANGUAGE = ‘FRENCH’. Nor will SQL join two teachers on that column. SQL doesn’t recognize repeating fields in a column because it already has a way to represent them: as rows. That a teacher speaks two languages is not one fact, but two; not surprisingly, those two facts appear in two rows. ‘Twas true in in 1977. ‘Tis true today. Thus ‘twill ever be.
- petalmind 2y ago> Because the meaning of "atomic" didn’t change, nor did the meaning of 1NF. So what is the meaning of "atomic"? Are array-typed values atomic? (I think you can treat it that way, a lot of lists are immutable in different programming languages.) > But he betrays very little knowledge of the relational model. This is absolutely possible. Let me ask a couple of questions to borrow from your understanding of relational model. > Repeating fields, which he proclaims as some kind of innovation, violate 1NF. They’re not atomic. Sorry, what is "repeating fields"? I don't remember ever using this, how does it look like? Are you talking about array-typed columns? If yes, then why are they not atomic? Also, I don't think I proclaim absolutely anything is "kind of innovation"? Postgresql supports array-typed columns since 2001, and it was planned in 1986 already as part of other "post-relational" features. Two schemas "(instructor_id, List<skill_id>); PK(instructor_id)", and "(instructor_id, skill_id); PK(instructor_id, skill_id)" are isomorphic, thus I may be wrong but I don't see any anomalies here. > Maybe the easiest way to understand it is that the list of languages can’t be used in SQL. If one teacher speaks Italian and French, SQL won’t find WHERE LANGUAGE = ‘FRENCH’. Ah, so you seem to be talking about array-typed columns. I mean, you can find it easily by using "WHERE languages @> ARRAY['FRENCH']". The difference is mostly syntactical. If you think that you are not allowed to access single elements of "languages" array because it violates "atomicity" of the value, then we should also say that you're not allowed to use queries like "WHERE name LIKE 'A%'", because they violate the atomicity of a string value. Don't you agree? > SQL doesn’t recognize repeating fields in a column Array-typed columns appeared in ANSI SQL:1999, a quarter of century ago. I'm not sure what you're talking about.
- mkleczek 2y agoThere are several issues with this: 1. 4NF is dependent on context. This context is described as dependencies. In the example of employees, skills and languages the design with two separate "link tables" employee_skills and employee_language is not obvious at all without specifying requirements _first_. If there is a requirement that an employee skill depends on the language (ie. an employee can type in a particular language only) - the design with a single 3 attributes table is the right one. 2. Multivalued attributes discussion is missing several important aspects for example referential integrity. It is not at all obvious what should happen when a language is deleted from languages table. Once you start adding rules and language to specify referential integrity for multivalued attributes you very quickly end up with... splitting multivalued attributes into separate relations.
- petalmind 2y ago> is not obvious at all without specifying requirements _first_. If there is a requirement that an employee skill depends on the language (ie. an employee can type in a particular language only) - the design with a single 3 attributes table is the right one. In the "Baseline" section I do exactly that: specify requirements first. Nowhere does it say anything about skill depending on the language, so I assumed that it implies that they are independent. "Suppose that we’re building a system that lets people find sports instructors. Each instructor has a number of skills they can teach: yoga, weightlifting, swimming, etc. Also, instructors can speak one or more languages: English, French, Italian, etc." Not sure how to improve this wording so that it was more clear that skills and languages are independent. > Multivalued attributes discussion is missing several important aspects for example referential integrity. It is not at all obvious what should happen when a language is deleted from languages table. I'm not sure what you mean here because multivalued attributes as defined in 1977 preserve referential integrity. But array-typed values do not, you're right. This is a very important consideration, thank you! I did not realize that (because I don't really think that classical enforced FKs are that crucial in modern practice.) But from a theory point of view having a list of IDs is not enforced, yes.
- mkleczek 2y agoIt would take a really long time to count all the issues caused by dangling references to nonexistent rows I had to deal with in my career. So yes - classical enforced FKs are still important - even if practice became „modern” (whatever that means )
- kazinator 2y agoEvery time I read explanations of any database anything, it looks childishly simplistic to what I've done with C pointers and structs. Basically it all exists because of storage devices: - large transfer factors (spinning platter hard drive track access, flash erase blocks) - latency (spinning platter track to track seek, and rotational latency) Once you have fast random access, it's data structures and algorithms: forget the database cruft.
- mkleczek 2y agoThat’s a very simplistic view. For once: you _always_ have to deal with memory hierarchy as there is no way to fit all data to the fastest memory. So you need to design with this in mind. Secondly: the point of databases is not to implement a single specific task efficiently but: a) provide means to maintain data so that it is easy to implement many different tasks (both current and future) b) to safely _share_ data between various tasks/programs/users
- kazinator 2y agoThere are all kinds of situations in which, if all your data fits into dynamic RAM, you don't have to give any other damn. (This is basically the bulk of the premise of technology like Python, for instance). Nobody ever rebooted their machine because it became unusable due to their L1 or L2 cache thrashing. You really have to care more about that particular hierarchy level transition, RAM to external storage, more than the others, because traditionally the transition is very steep, like suddenly hitting a brick wall of slowness. The higher levels hierarchies can be left to generic management mechanisms, like the hardware itself or compiler register allocations. The ram to storage transition is managed at run-time by software. Databases have to bypass the operating system version of that management, and provide their own, because it is so critical.
- petalmind 2y ago> Once you have fast random access, it's data structures and algorithms: forget the database cruft. I mean, yeah, but in practice you have to provide ACID or other invariants, you have to provide distributed processing and storage, you have to provide replication etc., etc. Also, "once you have fast random access" sounds like "once you have gajillion dollars". I too want a pony!
- somethingsome 2y ago>20 years ago, when I started with databases, I found natural to map a user object in my software with a unique 'user' table with many columns. (Excel style) It's only when I discovered how things were implemented and the required space and combinatorial complexity that the 4NF started to make sense. I think the natural 'naive' approach is to have many columns with redundant information inside. At some point, with more experience, we see how this system is harder to maintain (burden on the code logic) and how much space is wasted. I discovered 4NF (without giving it a name) by myself to solve those issues. The problem is that we teach 4NF to people that never implemented code with databases and had to solve the problems it generates if you only have multiple columns. So the examples seems artificial. When for the first time I saw 4NF in my lectures, it was easy as I knew all the issues without.
- petalmind 2y ago> I think the natural 'naive' approach is to have many columns with redundant information inside. Interesting, thank you. I either don't remember that phase in my own learning, or I was lucky to read about the "correct" approach first. Maybe that's why I was so extra confused about 4NF explanations, haha.
- miki123211 2y agoI think it depends on what intuitions you start out with. If you teach yourself programming as a kid like I have, and get to know about object-oriented programming very early on, normalization is more intuitive. "of course the addresses go in a separate table from the users, just as you'd have an `address` object instead of just putting all the fields inside `User`." If you start out using Excel a lot, and learn programming much later, the Excel way probably seems right at first. "Of course there's going to be a table with customer information, one row per customer, with separate fields for street, city, zipcode etc."
- somethingsome 2y agoYou are right, now that I think about it, I was using php prior to their implementation of object oriented. So I had no objects, the users were entities in my head, I had to keep track of their data, so a multi column entry in a database made totally sense to me at the time.
- vinay_ys 2y agoPerformance characteristics of the underlying hardware dictate the software abstractions that make sense. In today's machines, we still have differential cost for sequential, aligned, rightly sized block io operations vs random io operations of random sizes. And we have a hierarchy of storages with different latency, performance and costs – cpu caches, ram – and ram, ssd, spinning disks – and these via local attached vs in disaggregated and distributed clusters. So, if you want absolutely optimal performance, you still have to care about your column sizes and order of columns in your records and how your records are keyed and how your tables involved in join operations are organized and what kinds of new queries are likely in your application. This matters for both very small scale (embedded devices) and very large scale systems. For mid-scale systems with plenty of latency and cost margins, it matters a lot less than it used. Hence, we have the emergence of nosql in the last decade and distributed sql in this decade.