Hi all,

I am trying to add tracking capabilities to a messaging system we have.
I am struggling with the design, with a scenario which is pretty common I
would think.
We have 2 databases, one is the management database with all the
configuration values for the system. This is modified by admins of the
system, and read by the system at runtime.
The second database is the tracking db. This will be used for both invoicing
(based on messages passed through the system) and also troubleshooting
errors.

The issue I am running into is with the design of the tracking db. Initially
I created tables which tracked the key pieces of data, with IDs in each
table linking back to the management db, which contained the actual values.
Eg. tracking stored a reference to a party, but the party was defined in the
management db.
However, this design is going to fail when, say 2 years down the track the
management db no longer has a record for the party, because it was deleted
for example, but the tracking db still has a reference to it.
I'm not sure whether any of the below options I have been thinking about are
viable?:

- Copy ALL data from the management database when it is tracked. E.g. copy
the party record to a party table defined in the tracking db when a message
needs to reference it. However, this is going to cause the tracking db to
become huge. Maybe somehow do this with a check to the original management's
party ID, and when it is the same ID (i.e has not been deleted since the
last party was copied from management to tracking), use the same party in
the tracking system. When the management's party is not the same (based on
unique ID), create a new party in the tracking system.

- Have flags in the management db when an item is deleted, i.e. do not
delete it. That way the original reference from tracking to management will
always remain. Problem here is that you have old records in the mgmt db just
so the tracking db can reference it.

Any other ideas?!! Or any suggestions welcome!

Cheers,

Bardo.