Procedure_* tables and intended process

mwhitlaw wrote on Tuesday, August 10, 2010:

I am working to integrate a doctor with a lap.

May plan is to create HL7 order files by selecting and inserting data from and to
the procedure_* tables.

I will be using java and the HAPI library to create the files.
The lab I’m working with will handle the transport of the files
to and from the lab. (they only work with files).

Looking at the data model for this set of tables, there seems to be
an implied sequence to a procedure order. I would like to know if I
have it right, so here goes:

Before any procedure_orders can be created, there needs to be a procedure_type. Is that a correct assumption?

The procedure_type table has a procedure_type field which I assume corresponds to the
Group, Procedure Order, Discrete Result, Recommendation. Is this correct?

There is a lab_id in the procedure_type table. This would imply that the procedure_type table holds an
entry for each procedure AND lab. Also, to support this notion, the procedure_order table does NOT have
a lab_id. This would lead me to the statement:
If two labs perform the same procedure, there would have to be (at least) two entries in the procedure_type table
to represent that. Is this correct?

I don’t see how the lab_id gets into the procedure_type table. Related to this, since the
lab_id field is a bigint(20), it seems it would be a foreign key field to a labs table,
but I don’t see a labs table or a table that would hold labs. What am I missing?

Again, referring to the data model:
I see that procedure_result points to procedure_report which points to procedure_order.
Also, these foreign-key-like fields have indexes which are not unique, which would allow
for 1 to many relationships between
procedure_order(1) and procedure_report(many)
and likewise for procedure_report(1) to procedure_result(many)

In other words, give the notation: 1 -> many, these tables have the following relationship:

procedure_order -> procedure_report -> procedure_result

Is it expected that these one to many relationships will actually occur, or is the usual case
that there will be a one-to-one from procedure_order to procedure_report to procedure_result?

Finally, I’m trying to understand the implied sequence of events between a doctor and a lab and these
tables. This is where I am at this point:

1. Doctor requisitions an order which creates an entry in the procedure_order table.
2. My process pulls the new procedure_order record and turns it into an HL7 file
2. The specimen is put in the lab box for pickup.
3. The lab gets the HL7 order msg file and the specimen (at very different times)
4. The lab correlates the HL7 order with the specimen and sends back an acknowledgement of some kind
   which my process would receive and turn into a record in the procedure_report table.
5. The lab performs the requested test(s) on the specimen and transmits HL7 result msg file to the
   Doctor which my process receives and converts into records in the procedure_result table.

Have I got that right?

Also, the lab I’m working with does not do step 4. In that case, would I just create a dummy record
in the procedure_report table?