Is it bad design practice to repeat fields in a database in order to save yourself some convoluted queries?

If you have a few join tables which contain all the information you need, but it involves some complex joins etc.etc. would it be bett开发者_如何学运维er to stick an extra field in your table of choice to save yourself these extra queries ?

thanks in advance

Paul

Yes and no.

This is deliberately going against the principle of database normalisation, and is hence called "denormalisation".

Its bad points are pretty much all the reasons why we normalise, most notably that it introduces a way in which an error can make the database inconsistent with itself. In general therefore, it is bad DB design. In practice, it also makes updates to the database both more complicated and hence more expensive (and that's assuming no errors).

It can though give performance benefits to some queries that are the difference between always timing out, and being trivial. It is therefore a reasonable, and sometimes essential, optimisation.

I like to take the following approach to this:

1. Double think its necessity. In particular, CTEs have made some cases I would have resorted to this technique no longer necessary, as CTEs can do what used to require multiple table look-ups in just a couple (indeed, most cases where I resorted to it in the past). Sometimes also, all that's really needed is a thorough examination of the indices involved. Finally, it's worth trying the queries that have been optimised through the use of this denormalisation, without using it, every now and again; all sorts of changes can remove the need for it.

2. Create a separate table for the relevant data consider to be a "lookup" that is not considered part of the main design, highlighting to other developers that it is an optimisation rather than just bad DB design. This also makes it easier to rebuild the whole thing should an error cause the sort of inconsistency mentioned above.

3. Use triggers to maintain the table, so that updates to the tables it depends upon will automatically keep things correct, rather than having to introduce special rules about maintaining it when updating (those rules almost definitely will be broken, or at least lead to the code being less clear).

I've actually seen that happen and though i would be skeptic if it a good design practice, it seems that it can offer some good performance at times. In particular i've seen it as a counter cache in Rails, where a new field is presented in order to keep the count. I suppose that if you have too many entries, it can really produce some better performance.