I have a temporary report table which has a foreign key (
| id | val1 | val2 | source_id |
| -- | ---- | ---- | --------- |
| 1 | 102 | 0.5 | 1 |
| 2 | 99 | 0.9 | 1 |
| 3 | 212 | 0.1 | 2 |
and example of
| 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
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.