I have a temporary report table which has a foreign key (source_id), e.g. report table:

| id | val1 | val2 | source_id | | -- | ---- | ---- | --------- | | 1 | 102 | 0.5 | 1 | | 2 | 99 | 0.9 | 1 | | 3 | 212 | 0.1 | 2 |

and example of source table:

| id | descr | | -- | ------- | | 1 | source1 | | 2 | source2 | | 3 | source3 |

Because of the chosen design one cannot delete a source which is contained in the report (foreign key without ON DELETE CASCADE), e.g. cannot delete source1 or source2 but can delete source3. A report is generated for a specific list of sources selected by user.

I'm thinking to solve this problem at the service layer, i.e. add code which deletes the corresponding report rows in the service method which deletes a source. Is this a good solution? Are there better solutions? Or maybe the whole design could be improved?

NOTE: (1) The report is generated and stored in a table because of big amount of data and respectively long computation time. (2) The report cannot be defined as a view because of some specific computations that cannot be defined in SQL.

  • You stated that this is a temporary table? – Robert Harvey Nov 22 '18 at 16:07
  • Temporary in sense of its content, each time new reports are needed the table is truncated, then its content is filled with new reports – Radu Dumbrăveanu Nov 22 '18 at 19:32
  • So source is not a temporary table? – Robert Harvey Nov 22 '18 at 20:04
  • No, it is an ordinary table – Radu Dumbrăveanu Nov 22 '18 at 20:43

The problem

The reference to a source in temporary (throw-away) data prevents you from properly maintaining permanent source data that is no longer relevant.

If you delete temporary data corresponding to obsolete sources, you could create inconsistencies in the temporary report, and make your system appear unreliable:

  • if there are still users that browse through the results of the temporary report.
  • if the deletion occurs accidentally while the report is being generated

Alternative 1:

Design your reporting functions in such way that the temporary data that is no longer needed is deleted.

This tackles the problem at the root: sources are not blocked for deletion due to temporary data that is in reality no longer needed. In this case, only sources which are still needed cannot be deleted (and for a justified reason).

Alternative 2:

Design your source deletion function in two steps. First, a deletion marker is set. From then onwards, all the normal functions would no longer show a marked source: it's virtually deleted but still in the table.

From time to time run a cleaning function that looks like your deletion function of today, and which deletes all the items that are no longer referenced.

Alternative 3

Denormalize your temporary report data by copying the source information so that the temporary data is self standing snapshot and doesn't rely on anything that could change or disapear in the mean time.

Preferred alternative IMHO, 1 would be the best option.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.