3 ms·

I think the typical presentation of 4NF makes more sense when you consider the ergonomics of data processing systems when relational modeling emerged. Predating

I 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 ago
What 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 ago
  Sequential is still faster in a lot of cases even on SSDs and RAM (prefetching)
- petalmind 2y ago
  > Things that aren't normalized are "ad hoc," or really just "input." I agree that every denormalization is "ad hoc". It may not even feel this way because sometimes such ad-hocs become intimately tied into the system performance architecture. However I'm not sure if I agree with "just input", or maybe that's a different way of saying that: sometimes (often) you denormalize from 3NF to avoid a join. Often you can demonstrate that avoiding join is benefitial, by looking at query plan. But the problem with teaching is that people become generally more afraid of joins, somehow thinking that every join is killing performance or something. ChatGPT produces a lot of FUD around joins and performance, unfortunately...
kazinator 2y ago
Records had to be batched into blocks and blocks had to be organized for fast access on a spinning drum or platter. This is why we have B+ trees for databases, and not simple binary trees or whatnot. You need the branching factor in order to reduce the seek times: each tree node traversal goes to a different block of records. If they are on the same track, it takes rotational latency to get to the block. If they are on a different track, seek time plus rotational latency. Reducing rotational latency is why we got 7200 RPM and faster drives: going from 3600 to 7200 means any block is available twice as often at the drive head. It doesn't matter how many or how few have been crammed onto the track; density doesn't fix latency.
numpad0 2y ago
I wonder how many people actually have seen those index card shelves[1] IRL. Everyone have heard enough of music box analogy, the Tacoma Narrows, etc, but I don't remember this technology covered often enough. One set of shelves has cards for books sorted by its ISBN. Another by titles, by authors, by keywords, ... libraries(of books, of employee records, of manufacturing blueprints...) would have had whole rooms worth of these shelves for searching and indexing. Data normalization, SELECT, and JOIN, are just separating information on cards into separate shelves, hand picking cards, and laying out matching ones side by side on photocopiers. I've never had to mess with it, but apparently that was literally the database. 1: https://en.wikipedia.org/wiki/Library_catalog https://en.wikipedia.org/wiki/Library_catalog
petalmind 2y ago
Thank you, the second paragraph of your comment is illuminating. There are some more replies in the same vein. I did not understand the mutual independence of "cards", and how you can use a different data layout if you only need to put it on a single card. > with relational modeling as an academic concept well predating successful implementations of relational databases. This is also non-obvious, thank you!

pessimizer 2y ago

What 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 ago
Sequential is still faster in a lot of cases even on SSDs and RAM (prefetching)
petalmind 2y ago
> Things that aren't normalized are "ad hoc," or really just "input." I agree that every denormalization is "ad hoc". It may not even feel this way because sometimes such ad-hocs become intimately tied into the system performance architecture. However I'm not sure if I agree with "just input", or maybe that's a different way of saying that: sometimes (often) you denormalize from 3NF to avoid a join. Often you can demonstrate that avoiding join is benefitial, by looking at query plan. But the problem with teaching is that people become generally more afraid of joins, somehow thinking that every join is killing performance or something. ChatGPT produces a lot of FUD around joins and performance, unfortunately...

kazinator 2y ago

Records had to be batched into blocks and blocks had to be organized for fast access on a spinning drum or platter. This is why we have B+ trees for databases, and not simple binary trees or whatnot. You need the branching factor in order to reduce the seek times: each tree node traversal goes to a different block of records. If they are on the same track, it takes rotational latency to get to the block. If they are on a different track, seek time plus rotational latency. Reducing rotational latency is why we got 7200 RPM and faster drives: going from 3600 to 7200 means any block is available twice as often at the drive head. It doesn't matter how many or how few have been crammed onto the track; density doesn't fix latency.

numpad0 2y ago

I wonder how many people actually have seen those index card shelves[1] IRL. Everyone have heard enough of music box analogy, the Tacoma Narrows, etc, but I don't remember this technology covered often enough. One set of shelves has cards for books sorted by its ISBN. Another by titles, by authors, by keywords, ... libraries(of books, of employee records, of manufacturing blueprints...) would have had whole rooms worth of these shelves for searching and indexing. Data normalization, SELECT, and JOIN, are just separating information on cards into separate shelves, hand picking cards, and laying out matching ones side by side on photocopiers. I've never had to mess with it, but apparently that was literally the database. 1: https://en.wikipedia.org/wiki/Library_catalog https://en.wikipedia.org/wiki/Library_catalog

petalmind 2y ago

Thank you, the second paragraph of your comment is illuminating. There are some more replies in the same vein. I did not understand the mutual independence of "cards", and how you can use a different data layout if you only need to put it on a single card. > with relational modeling as an academic concept well predating successful implementations of relational databases. This is also non-obvious, thank you!