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