Search code examples
databaseperformanceforeign-keysdenormalizationdatabase-indexes

DB design and performance: Is it OK to use redundant FK to increase performance?

Lets say I have following DB structure:
Normalized DB structure
and my application needs to show the list of articles with all the details (model, product family, brand, producer). For that I would need to make more JOINs to get needed data.

Is it OK if I increase application's performance by creating redundant FKs to the Article table like follow? Does it actually increase the performance?
DB structure with redundant FKs

Solution

  • Yes you can increase performance that way if you don't want to retrieve any data of "intermediate" objects in the hierarchy. This is a common form of denormalization. Notice, that you need to be careful not to let inconsistencies slip in.

    I usually set up a nightly task that verifies the denormalized data, mails errors to me and automatically fixes them. This is not hard to do and eliminates a nasty class of bugs.

    A different reason why people do is is to partition all tables on the same key.