> Most financial transactions have multiple time dimensions: a trade is requested, booked, filled, matched, reconciliated, paid, settled at different times. These times often don't even follow a perfectly logical order (you can request and fill a trade and book it an hour later).
Similar issues exist with transaction identifiers. Every party involved with any aspect of a transaction may assign that aspect of the transaction zero or more identifiers, those identifiers may or may not be unique over any particular set of transactions and/or events related to transactions, and the sources of those identifiers may or may not even correctly document what, exactly, those identifiers identify. (And their representatives may think they know, and they may be wrong.) Your own code may need to generate identifiers for events associated with transactions, and you may or may not want to leak information in your choice of identifiers.
For new designs, my suggestion would be to treat these as one-to-many relations. Have a table along the lines of (transaction id, event, identifier issuer, identifier name, identifier). You might also need a timestamp in there, and you may need to throw in a row id of some sort as well. You might want to make an allowance for identifiers having variable type — are they bytes? Unicode? something else? You could plausibly get away with making these be JSON keys in an event object instead, but this gets awkward when someone tells you an identifier after the fact.
There is an interesting tension in software between measurement and validation. The two concerns are often conflated, resulting in software systems that cannot measure the world as-it-is. The world (more precisely, measurements of the world) may be in (and often is in) an 'invalid' or 'inconsistent' state. Software can only help you detect and resolve such states if it is capable of representing them.
Similar issues exist with transaction identifiers. Every party involved with any aspect of a transaction may assign that aspect of the transaction zero or more identifiers, those identifiers may or may not be unique over any particular set of transactions and/or events related to transactions, and the sources of those identifiers may or may not even correctly document what, exactly, those identifiers identify. (And their representatives may think they know, and they may be wrong.) Your own code may need to generate identifiers for events associated with transactions, and you may or may not want to leak information in your choice of identifiers.
For new designs, my suggestion would be to treat these as one-to-many relations. Have a table along the lines of (transaction id, event, identifier issuer, identifier name, identifier). You might also need a timestamp in there, and you may need to throw in a row id of some sort as well. You might want to make an allowance for identifiers having variable type — are they bytes? Unicode? something else? You could plausibly get away with making these be JSON keys in an event object instead, but this gets awkward when someone tells you an identifier after the fact.