CODEWITHSUNDEEP
database-design
Greg
Greg

Reputation: 7383

Best way to Display/Update Derived information?

A common issue I am faced with is having derived information that must be displayed on detail pages and in overview tables.

For example, there maybe a status for 1 item based on the status of multiple sub-item's status value. It seems storing the derived status for the main item would be wrong but sorting and filtering for this information can be troublesome as well.

What is the best way to handle this situation?

Upvotes: 1

Views: 226

Answers (1)

Sergii Vozniuk
Sergii Vozniuk

Reputation: 222

I'm not sure, that I got your question correctly. To explain how I understood your question I'll use example.

Example: we have a car (item) and its parts: wheels, engine, doors etc. (sub-items). The status of the car is derived from the status of its parts. For example: if the status of engine is 'broken' then the status of the car is 'broken', or if the status of doors is 'missing' and engine is 'OK' then the status of the car is 'usable'.

If this example corresponds to what you meant then you have following options:

  1. Calculate the derived value each time you show the status of the car, generate report etc. Advantages: the model is always consistent. Disadvantages: calculating may take too much time and resources.
  2. Calculate the derived value the first time and store it in the database. Change it every time the status of sub-items change (trigger, stored proc, business logic). Advantages and disadvantages are the same as in 1, but if you choose between 1 and 2 you have to consider what happens more often: a status of sub-items changes or the information about the item is requested.
  3. If the database is highly loaded and the information about items is not time-critical, then you may recalculate the statuses of items over-night when the load on your database is minimal and store them in the item field. Advantages: minimizes the load. Disadvantages: the returned value of derived field may be incorrect.
  4. Save the statuses of sub-items when the derived status was last calculated. Then compare current statuses (when the status of item is requested) with saved one's. If changed then recalculate else return stored value. Advantages: lowers the load. Disadvantages: requires additional fields for last values of statuses.

These are the options, but only you know specific information (database load, complexity of derived value calculation algorithm, what is more often sub-items statuses change or item statuses reads etc). So only you can decide what suits with your case.

Upvotes: 1

Related Questions