Re:Maybe Normalizing Isn't Normal
by michaelbuckbee on Tuesday July 15, 2008 @09:53AM
(#246636)
I'm not sure what you mean by "handle the tables internally", but as a really rough exercise, you can think of just raw table reads.
If you have 6 tables that need joined, you're going to need to "look through" each to find the ones with a matching id. If it's all one table it's essentially just one read.
Now, there are many many caveats to all of this (how indexes are setup, relative sizes of tables, etc. but in general it is more computationally expensive to do more joins).
In theory the db could keep the separate tables in one table internally. But granted, probably it won't always do so.
Yeah, this is called a materialized view.
This depends heavily on implementation details. If you are using Oracle (rare, I know for any company not in the Fortune 500), then just make a materialized view and you are set. Oracle will pre-compute most of the data in the view and ensure consistency (<<-- Not an oracle DBA so some confirmation here would be nice). Furthermore, some database engines handle joins and the kind a lot more efficiently than others.
Furthermore, some kind of query caching mechanism will always be a better choice than de-normalization and most decent databases have that inbuilt (and if you really want, you are welcome to add memcached on top of that though that is not guaranteed to be correct). Either way, there are a lot more options than straight out de-normalization.
I think it is OK to cache some things, like aggregated values (for example how many friends does a user have). But those cached values (or views) I would not consider part of the actual base data model. Even though those values might be stored in the same db, I would consider them to be another layer in a way.