1

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

Freiheit
  • 8,408
  • 6
  • 59
  • 101
user3288577
  • 45
  • 7

2 Answers2

1

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.

agermano
  • 1,679
  • 6
  • 16
0

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

Neil VanLandingham
  • 1,016
  • 8
  • 15