Impossible Relationship? Referential Integrity on tables with on From Date and To Date

[HGT]

Hello all,

I am currently on a project which the source data come into the
databases is always dirty (not surprisingly); however, due to the
design of the database, it is very difficult to impose referential
integrity (esp. Foreign Keys) on these tables. Am I just not thinking
straight? This solution is implemented on multi RDBMS.

Example, we have 2 tables, one with a number of columns and a TIMEID
column to specify the load date (in YYYYMM), on the table, there is a
number of other columns which are IDs and has to refer to other tables.
One example would be currency (CURRENCYID) and it refers the CURRENCY
table. To ensure we capture history on these tables, we make use of a
from date and a to date (FROMDATE and TODATE) on these tables. As the
currency list updates very infrequently, the FROMDATE is represented in
YYYYMM and TOTIME is NULL if it is the most current record.

One problem is that when we try to form a foreign key relationship, as
the column names are not the same and it is not an equality
relationship. The only solution is to implement triggers on the
database. As this is cross database solution - triggers would not be
the easiest way to do it. Did we design it all wrong? I am sure someone
must have seen this problem before.

Many thanks in advance.

Regards,
HGT

[Knut Stolze]

HGT wrote:

Hello all,

I am currently on a project which the source data come into the
databases is always dirty (not surprisingly); however, due to the
design of the database, it is very difficult to impose referential
integrity (esp. Foreign Keys) on these tables. Am I just not thinking
straight? This solution is implemented on multi RDBMS.

Example, we have 2 tables, one with a number of columns and a TIMEID
column to specify the load date (in YYYYMM), on the table, there is a
number of other columns which are IDs and has to refer to other tables.
One example would be currency (CURRENCYID) and it refers the CURRENCY
table. To ensure we capture history on these tables, we make use of a
from date and a to date (FROMDATE and TODATE) on these tables. As the
currency list updates very infrequently, the FROMDATE is represented in
YYYYMM and TOTIME is NULL if it is the most current record.

One problem is that when we try to form a foreign key relationship, as
the column names are not the same and it is not an equality
relationship. The only solution is to implement triggers on the
database. As this is cross database solution - triggers would not be
the easiest way to do it. Did we design it all wrong? I am sure someone
must have seen this problem before.

I don't know if I got this right... Do you want to have this:

(1) One table T ( TIMEID, DATE_VALUE )
(2) Another table C ( CURRENCYID, FROMDATE, TODATE )

Where C(FROMDATE) and C(TODATE) both refer to T(DATE_VALUE) (or T(TIMEID)).
Or is it the other way around and you want to establish an integrity
constraint that says that your DATE_VALUE must be somewhere in the interval
defined by [FROMDATE, TODATE]?

The first case will simply work, assuming a proper definition of the data
types. The second case with the ranges will not work as referential
constraints always test for equality and not for BETWEEN. Triggers would
be a way to solve the issue.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany