> Normalize until it hurts, denormalize until it works. (I.e. prefer normal forms but denormal will have better performance.)
No it won't, it depends entirely on the situation. Denormalised data (besides allowing update discrepancies) can expand the amount of data, thus push otherwise memory resident working sets onto disk. Big slowdown. You are wrong. The correct answer is "it depends" - and denormalising really is a last resort that has to be done judiciously and with care.
> You need nullable dates and nullable numbers. You never need a nullable string.
Utter unjustified garbage. Correct answer (from a practical, not super-normalised form) is: "it depends"
> When in doubt, just have a column full of JSON and figure it out later.
OMG please never let me come near any fool who does this. I was called in to consult on a database problem and they did the same but with XML. It was really bad, and bad for performance and comprehension.
>I was called in to consult on a database problem and they did the same but with XML. It was really bad, and bad for performance and comprehension.
I can see XML being really bad for performance, but given that json column data is handled like native data in modern databases (and you can index/query on fields in the json, which pretty much negates the performance impact), using a json/jsonb column is actually a decent guide.
You are spot on in picking me up on this, thanks. It needs clarification. xml/json is neither right nor wrong, it depends. Performance, well, it depends. What is unquestionably wrong is the poster's advice "When in doubt, just have a column full of JSON and figure it out later".
Pouring structurable data into an unstructured heap is just laziness and will hurt you later. I've seen the cost of deciding to 'leave it for later' - it's high! I've been involved in cleaning it up. It's horrible.
Years ago I picked up a book written in the 1960s (so pre IT)for secretaries, and it had a beautiful line in it "the filing cabinet is a place where you find things, not lose them". Still applies. Always will do.
> "the filing cabinet is a place where you find things, not lose them"
"Those who cannot remember the past are condemned to repeat it." - Santayana
“Explanations exist; they have existed for all time; there is always a well-known solution to every human problem—neat, plausible, and wrong.” - Mencken
Software project management is full of thin and runny rationalizations...I (HHOS) include this form under the heading of "Data Abnormalization"...
I've had a lot of experience. You can learn from it or not. If not, feel free to prove Santayana right again, and at great personal cost :) In fact, I encourage you to!
Is there a reason then not to use a single column with JSON? The columns would then be "virtual" (the column jsondata would spawn a column jsondata.firstname etc.)
This question is your of pure curiosity, I do not deal with databases so I hopefully correctly understood your comment.
I believe the right answer is somewhere in the middle.
I know a couple of successful products where the dB scheme is completely abused. Yet commercially a succes for over 8 years.
A good dB specialist also knows the implications of a 'bad' design choice. It's all about the impact at a later moment. Just like software design in general. Finding the balance between complexity and simplicity. Between slow and elegant versus fast and ugly.
Balance is key
I did say clearly it depends. And that's because it does.
> Between slow and elegant versus fast and ugly
No! Unqualified, this is a false dichotomy! Because often you can have elegant and fast, and that's because elegance of design often gives you that speed. Not universally true, but much more often than not.
>>I did say clearly it depends. And that's because it does.
You've arrived a tad hot to the discussion to now start to backtrack on how it depends.
After all, if it depends for your defense then shouldn't it also depend for the OP's point? Because if you know that it depends but still start to accuse others of trolling, you are not being helpful nor honest.
Wasting your design time allotment in rationalizing futile compromise is the flip side of seeing everything as a dichotomy. The reality modeled by human discourse may be only consensual, not actual...
Guy with actual DB experience here, so:
> Normalize until it hurts, denormalize until it works. (I.e. prefer normal forms but denormal will have better performance.)
No it won't, it depends entirely on the situation. Denormalised data (besides allowing update discrepancies) can expand the amount of data, thus push otherwise memory resident working sets onto disk. Big slowdown. You are wrong. The correct answer is "it depends" - and denormalising really is a last resort that has to be done judiciously and with care.
> You need nullable dates and nullable numbers. You never need a nullable string.
Utter unjustified garbage. Correct answer (from a practical, not super-normalised form) is: "it depends"
> When in doubt, just have a column full of JSON and figure it out later.
OMG please never let me come near any fool who does this. I was called in to consult on a database problem and they did the same but with XML. It was really bad, and bad for performance and comprehension.
Please nobody listen to such 'advice'.