> Adam, I don't understand why you have keys to both TableA and TableB in
> TableC. Do your business rules allow records to exist in C that

have links

> to A but not to B (or vice versa), or rows in B that are not linked

to A?

>
> ./heLen

Hi Helen,

Sort of. TableC is absolutely linked to TableA. TableB has changable
startdate and enddate, so a change on the startdate of a record in
TableB could see a record in TableC changed from one to another, or
changing the Date of TableC might change it to another record in TableB.

TableC.TableB_ID used to be a computed field, but for performance
reasons we converted it to a real field and have triggers to maintain
the relationship. It is indexed, and is the basis for many queries etc.

For a record in TableC to be legal, it must have a record in TableB.
Hence triggers watch TableB StartDate and EndDate, and TableC.Date,
and any change forces the TableB_ID to be re-evaluated.

There lies the problem, in order to move the database from one "legal"
state to another "legal" state, it must move through an "illegal"
state (in a business rule sense). It is never "committed" in the
illegal state, but my current logic totally prevents the illegal state
from occurring, so it is impossible to change without temporarily
bending the rules.

So.

Table B
(ID, TableA_ID, StartDate, EndDate)

1,1,2 Jan 2005, 1 Jan 2006
2,1,2 Jan 2006, 1 Jan 2007

Table C
(ID, Date, TableA_ID, TableB_ID)

1, 5 Jan 2006, 1, 2

The above would be a legal state. Now say the user was to change
TableB to something like

1,1,2 Jan 2005, 1 Feb 2006
2,1,2 Feb 2006, 1 Jan 2007

ie, They have extended the first record by a month.

Table C would now look like

1, 5 Jan 2006, 1, 1

You can see that two update queries were run on Table B. The rules are
(in simple terms) that the ranges can't overlap, and that

select count(*)
from tableB
where ID = [table1.tableb_id]
and StartDate <= [table1.date]
and EndDate >= [table1.date]

count(*) must be > 0

I can not change from one to the other without breaking one of the rules.

Adam