I've this set of tables:
- A hold some choices made by user
- X groups A records
- B hold some other choices made by user
- C hold all the possible choices that can be put in B and
parametrised criteria when they can be chosen according to values
stored in A
something like
create table X (
id serial primary key
);
create table A (
fkid int not null references X (id) on delete cascade
fieldA1 int,
fieldA2 int,
fieldA3 int
)
create table C (
id serial primary key,
crit1M int,
crit2M int,
crit3M int,
...
)
create table B (
fieldB1 int not null references C (id) on delete set null,
...
)
now fieldB1 could be chosen among
rule:
select C.id from (
select sum(A.fieldA1) as c1, [some other function on A]
where A.fkid=[some id in X]
) as _subquery
where _subquery.c1<C.crit1M and ....
now the user get a list of valid values from the above query, chose
one that is placed in table B.
Now I'd like that if user change the status of A, B.fieldB1 get
checked so that B.fieldB1 respect the "rule" or is set to null.
Actually in B there are more fields, each one that is dependent on a
rule similar to the above. Fields in B will be filled from user input
in different not strictly ordered stages.
The simplest thing I can think of would be to write a trigger that
get called every time A is changed, but that's far more DB coherency
I need, and the check will be far more frequent than what's
reasonably needed even if I do appreciate the fact that the DB will
always be in a coherent state.
Another naive approach would be to add to B a Validated field and set
it to false every time something in A get changed.
My forecast anyway is that the "rule" is going to extend its
dependency further outside table A, so I'd have to plan a cascade of
triggers that's going to make this stuff hard to maintain and
resource hungry.
That makes me think there is no silver bullet.
Last resort to make things more manageable from the application point
of view would be to access B just trough functions, so that every time
B is actually accessed rules will be verified. Read will return null
if fields aren't valid anymore and write will return a code
accordingly to successful write or not and this is as much lazy as
the thing may get.
But maybe I'm not aware of some silver bullet technique/tool pg may
offer or I'm just thinking loud or looking for moral support...
thanks for your thoughts
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
