Project

General

Profile

Feature #7221

Referential integrity for database arrays

Added by Radko Krkoš 2 months ago. Updated 4 days ago.

Status:
New
Priority:
Low
Assignee:
Category:
Research and analysis
Target version:
Start date:
04/16/2021
Due date:
% Done:

0%

Estimated time:
To be discussed:
No

Description

A classic referential integrity model in relational databases is based on FOREIGN KEYs, implemented as REFERENCES in the PostgreSQL DB. This feature has several advantages, preventing irrecoverable errors in data and allowing CASCADE of data modification procedures.
The database system used currently does not support references for array values, which are used commonly in the Mentat database model and missing this feature caused several inconsistencies (albeit with minimum impact so far).
A workaround at the database level might be a using a TRIGGER, checking the data during all modifications. There are two paths that must be analyzed: INSERT/UPDATE to the referring array and INSERT/UPDATE/DELETE to the KEY column/table. Both should be doable using the RIGGER approach.
Things to consider:
- implementation feasibility,
- performance impact in all scenarios (read should not be affected, others must be analyzed),
- parametrisability/reusability (there are several such reference relations, with more to come).


Related issues

Related to Mentat - Bug #7215: Wrong usage of DB array in mentat_main/settings_reporting/emailsNew04/15/2021

Actions
Related to Mentat - Bug #7220: Erroneous values in mentat_main/reports_events/mail_toNew04/16/2021

Actions
#1

Updated by Radko Krkoš 2 months ago

  • Related to Feature #7052: Link report to each group which owns it added
#2

Updated by Radko Krkoš 2 months ago

  • Related to Bug #7215: Wrong usage of DB array in mentat_main/settings_reporting/emails added
#3

Updated by Radko Krkoš 2 months ago

  • Related to Bug #7220: Erroneous values in mentat_main/reports_events/mail_to added
#4

Updated by Radko Krkoš 4 days ago

  • Related to deleted (Feature #7052: Link report to each group which owns it)
#5

Updated by Radko Krkoš 4 days ago

Removing relation to #7052, as it was dealt with differently, based on relational model.

Also available in: Atom PDF