What might be optimized way of processing HL7 data in Mirth and Database?

Question

We get HL7 ADT messages from hospitals on Mirth Interface. We get diagnosis and procedure codes in each message and we want to store all 50 diag and proc codes. Note= We also want to capture related\associated fields with each of these codes. Like every diagnosis code has code date, description, type, category, poa, group.

• If we capture all these in separate variable and store all in columns - So for each patient around 400 cols populated in one insert
• If we store as rows then for each code, a separate insert statement is triggered. This also degrades the performance

What might be best approach to process this in mirth and store in DB - So if for patient x ADT Message 1 will insert 3 diagnosis codes ( with related fields). Message 2 can append and will insert may be 5 codes in total ?

Thanks

Answer 1

It sounds like you want to perform an UPSERT operation, a.k.a INSERT ... ON CONFLICT UPDATE. If you are using PostgresSQL, you will find this link helpful: https://wiki.postgresql.org/wiki/UPSERT

Answer 2

If you are worried about insert performance, probably the best thing to do is one row per code, with a timestamp of when it was inserted. Don't worry about updating an existing row if the code already exists for that patient. Insert a new row with the current timestamp. When pulling codes for a patient, assume there may be duplicates and always pull the row with the most recent timestamp for a given code.

If desired, you can have a clean-up process that runs outside of peak hours to delete older rows that are no longer needed.

The alternatives require locks and/or checking existing values before deciding to update or insert.