3 ms·
Not a db expert, but I can't imagine joining tables is much of a problem. At least in theory, the db should be able to handle the tables internally as if it was
by Tichy 18y ago
Not a db expert, but I can't imagine joining tables is much of a problem. At least in theory, the db should be able to handle the tables internally as if it was only one table. Not sure if all db engines would be good enough at optimizing, but it is probably one of the easier approaches to performance tuning. Therefore I would expect that most db engines already handle that nicely.
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.
- michaelbuckbee 18y agoI'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).
- Tichy 18y agoIn theory the db could keep the separate tables in one table internally. But granted, probably it won't always do so.
- olavk 18y agoYeah, this is called a materialized view.
- newt0311 18y agoThis 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.
- olavk 18y agoSQL Server supports "indexed views" which is basically the same as materialized views.