Adding foreign key constraints in archive tables

Question

Say I have these tables and Oldbookings is an archive table containing rows from Bookings made a year ago. Also, assuming that such constraints were already defined in Bookings:

FOREIGN KEY (cusNo) REFERENCES Customers(cusNo)
FOREIGN KEY (flightNo) REFERENCES Flights(flightNo)

Would it make sense to define these contraints (foreign key) in the archive table OldBooking?

---

Customers(cusNo, cusName, DOB)
Flights(flightNo, destination)
Bookings(cusNo, flightNo, date)

OldBookings(cusNo, flightNo, date)

---

Answer 1

It depends on the business requirements.

The presence of an OldBookings row referencing a flight or customer would not allow that flight or customer to be deleted or moved. It ensures that OldBookings references a valid flight and customer.

Answer 2

In the abstract, I'd say don't break the BOOKINGS tables into CURRENT_BOOKINGS and OLD_BOOKINGS. If you need to subdivide the table, perhaps you could use partitioning to make logical subtables.

If you do need to maintain an off-line or secondary-system history, it's generally not a bad idea to denormalize the OLD_BOOKINGS table to capture the information you need in a flat record structure, like this:

**BOOKINGS**
BOOKING_ID
CUSTOMER_ID
FLIGHT_ID
...

**OLD_BOOKINGS**
BOOKING_ID
CUSTOMER_ID
CUSTOMER_COLUMN_1
...
CUSTOMER_COLUMN_N
FLIGHT_ID
FLIGHT_COLUMN_1
...
FLIGHT_COLUMN_N
BOOKING_ATTRIBUTE_1
...
BOOKING_ATTRIBUTE_N

Answer 3

"Duplicating" such constraints in the archive DB would secure you against flaws in the archival process itself.

Up to you (or perhaps up to your users) to decide whether or not you want to guard your archives against such flaws.