One of the goals of this substack is to research ways of removing historical cruft from the way relational model is taught. One thing that puzzles me particularly is why Sixth Normal Form (6NF) is historically treated so... reverently? Lots of texts on the internet consider 6NF to be “exotic”, “academic”, “never used in practice”, etc., etc.
As software developers, we can solve any problem by adding one level of abstraction, and database modeling is not an exception. I’m going to show that any database could be represented as a number of relations in either 6NF or 4NF, and why it’s useful.
NB: Any database is subject to 6NF decomposition
We won’t change the original database schema. Our 6NF relations would be purely virtual, like table views or just subqueries.
Using this approach, we can dissect any database, no matter what the physical table design strategy it uses. We can handle everything:
classic textbook 3NF approach;
Entity-Attribute-Value (EAV);
array-typed columns, like in Postgres;
JSON-typed columns of most forms;
etc., etc., including of course the usual uhm… hybrid mix that can be seen everywhere;
... continue reading