Kontrolleret redundans

Følgende beskriver det vist meget godt:

Performance versus Redundancy

At the beginning, just two sentences:

Querying (reading) a database needs more time, if many tables (low grade of redundancy) are accessed and need to be joined.

Writing access to a database is better supported and error-proof, when using redundance-free tables.

Both tasks are contradictory and a decision about the suitable structure, depending on the use of the database, is necessary. In certain cases, controlled redundancy does make sense, whereas uncontrolled redundancy shall always be avoided.

Four kinds of redundancy can appear:

1) derived attributes: can be calculated from other attributes in the database but need a lot of time to be computed. When you store these derived attributes in separate fields or tables, ensure that every concerned derived attribute is computed again when the basic attributes change. It doesn\'t help, when the basic attributes (e.g. price) changes, but the derived attributes (turnover) doesn\'t change too.

2) denormalisation: when denormalisation is requested, demonstrate, which queries perform better after denormalisation. Otherwise it is doubtful, whether the higher effort of checking data-integrity is justified.

3) temporary stored views: this kind of views is in between tables and views. They are used, when a lot of involved tables lead to long processing-times. Provisional results are stored in a table and the final query computes the result from these temporary stored views. This kind of tables (aggregates, etc) are most often used in Datawarehouses.

4) Replication: very similar to temporary stored views is replication. It is often used, when the network-performance on distributed clients is low or no network-connection possible (sales agent visits customers at their location/home). Then the database is replicated (copied) and used locally. The task gets more complex, when the replicate database is also used for creation, change and deletion of data.

Sagt på lidt mere forståeligt dansk:

Kontrolleret redundans er (sådan som jeg forstår det), når man omgår nogle af de normale normaliseringregler og indfører redundans i nogle tabeller for at kunne spare nogle langvarige queries (der joiner mange tabeller) og derved øge hastigheden.

Jeg takker for svaret... så har jeg også lært noget i dag :-)

Og undskyld det lige tog lidt tid inden jeg fik læst dit svar... har været lidt optaget de seneste par dage.

Det er skam helt ok. Sådan kan vi vel alle have det engang imellem.