[deleted]
5NF and Database Design
61–70 of 86 posts
Re: 5NF and Database Design
#62There are use cases where is better to not normalize the data.
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.
Doing reports or lookups for stuff inside json is the death of any performance, however.
Re: 5NF and Database Design
#63Earlier quoted context omitted.
> Your database disk usage by table report is going to be dominated by junction tables, foreign key constraints, and indexes, and all you're really buying with that disk space is academic satisfaction. FK constraints add a negligible amount of space, if any. The indexes they require do, certainly, but presumably you're already doing joins on those FKs, so they should already be indexed. Junction tables are how you re…
> Junction tables are how you represent M:N relationships. Yeah, the problem is that when you get to 4NF+, you're often looking at creating a new table joining through a junction table for a single multi-valued data field that may be single values a plurality or majority of the time. So you need the base table, the junction table that has at least two columns, and the actual data table. So, you've added two tables, t…
Right. But faceting data is also part of what a good database designer does. That includes views over the data; materialisation, if it is justified; stored procedures and cursors.
I've never had to do 18 joins to extract information in my career. I'm sure these cases do legitimately exist but they are of course rare, even in large enterprises. Most companies are more than capable of distinguishing OLTP from OLAP and real-time from batch and design (or redesign) accordingly.
Databases and their designs shift with the use case.
Re: 5NF and Database Design
#64Earlier quoted context omitted.
Why shouldn’t we care about layer 2? You can do really fun and interesting things at the MAC layer.
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". D…
OSI is particularly obnoxious because layers 5 and 6 don't exist separately in practically any system. Application layer protocols handle them in their own bespoke ways, so we have a software stack consisting of layers 2,3,4,7 like its the pentatonic scale or something.
Re: 5NF and Database Design
#65The 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?
Frankly 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/#understan...
I'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.
Re: 5NF and Database Design
#66For 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?".
Re: 5NF and Database Design
#67güzel elinize sağlık
Re: 5NF and Database Design
#68the 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
Re: 5NF and Database Design
#69i like it but i find the writing style difficult to read.
Re: 5NF and Database Design
#70i like it but i find the writing style difficult to read.
Could you share an example of writing stule that you enjoy?