Quote:
Originally Posted by Gullanian
I would say optimising on table width is premature optimisation. Not heard of table width being a factor before but looked it up and apparently it is.
This isn't what I was suggesting at all. I simply said that aiming to 4NF tends to faster tables due to much fewer nulls and narrower tables. I was responding to the 99% nullspace and added the narrow tables as a bonus, not a goal. The fact is that so much null is going to do damage to the database memory and speed.
irt to premature optimization: he is talking about 400M row tables, which is pretty large and would requires some tricks to get down to 500ms.
Quote:
Overnormalisation is a thing, even though I suppose you could normalise things like title and description I would never do it unless qualities of the data make it appropriate (bulk of titles/descriptions are repeating). It overly complicates something that should be simple - if you want to update the description now you have a much longer process.
I suppose it depends on the RDMS you are using, but any system with a returning value makes this process fairly simple.
Depending on who's opinion matters to you, 4NF should always be the goal. Anything less generally results in unwanted anomalies.
Quote:
I don't think that's anti conventional wisdom at all, normalisation will of course help with query performance. Denormalisation is always used to increase performance.
Speed is not a consideration of normalization. It happens that proper text-book normalization will often result in faster database queries vs a poorly designed database. The strategy I propose is normalize then denormalize the hot-spots. There really won't be many.
To be sure we aren't talking past each other, normalization only refers to a single relation, not the entire database.
I'm not sure why you wouldn't do this in your example:
Code:
users
--------
user_id
... etc ...
emails
----------
email_id (pk)
from_user_id (fk to users)
to_user_id (fk to users)
subject
body_text
... etc ....
It's normalized, requires no complicated joins, and would be incredibly fast.