Re: Need suggestions - foreign key issue

>Hi folks,
>I am looking for solutions for the following design problem:
>
>I have an invoices table and payments table. Payments table has a
>foreign key to a field in the invoices table. Now if the invoice is
>paid it needs to be deleted from the invoices table and moved to the
>invoices_history table. Now I cannot move the record from the invoices
>table to the invoices_history table because it has a foreign key
>associated to it.
>
>I can possible solve this issue by having a payments_history table which
>has a foreign key relationship with the invoices_history table. But then
>I have a number of programs refering to the payments table which now
>will have to look in payments_history table as well. Due to the large
>volume of records, doing a UNION operation is time-consuming. Should I
>then create a view combining the records of payments and
>payments_history table so that all the programs and can refer to one
>table? Although I can create a view as UNION of payments and
>payments_history tables, Sybase system10 does not allow such an
>operation.
>
>I need a solution which will fit for Oracle7, Sybase 10, MS SQL server 6
>and Informix.
>
> Thanks in advance.
>

How about a HistoryFlag on the table and clean it out to the actual history table once a year or so for stuff that doesn't get accessed. Not exactly pure, but it'll work.
Gary Received on Sat Jan 04 1997 - 00:00:00 CET