Looking for a good method of extending external data with an internal database table

Question

I have an application that receives most of its data from a third party. One of these sets of data is a transaction list. I can't write to this service, but I want to add more information to that data for use in my own application. I intend to do that with a SQL table with extended info.

The problem is that the third-party data does not return a record identifier from their side. That means I can't use a third-party primary key as the primary key for my additional data. If I could, I'd obviously just use that key and store extended data.

For example's sake, the data returned is:

-----------------------------------------------------------------------------
| Client Id | Transaction Date | Transaction Amount | Description | Balance |
-----------------------------------------------------------------------------

Client Id is not unique in this table, however, there is a guarantee that the rest of the information, taken in its totality, is unique.

I want to add additional data. For example:

-------------------------------------------------
| ... | Transaction Category | Hide Transaction |
-------------------------------------------------

I've toyed with the idea of using a primary key that is a hash of all of the other information, but querying for that data would be very inefficient as far as I could see. For example, I may want to display 100 transactions on screen. That would require retrieving the data from the third party, hashing each record, and querying my local database for all data with one of those 100 keys.

Does anyone have any suggestions?

Answer 1

I think you answered your own question:

there is a guarantee that the rest of the information, taken in its totality, is unique.

Just make those fields into a composite primary key. Your WHERE clauses will be a little more complex than normal but that's the trade off.

You could also create a surrogate key that would work with the composite key if you wanted to make your queries a little simpler.