Current design

The location table in EMAP Star has a primary key (location_id) and foreign keys to department, room and bed tables, which record metadata for each of those entities. This makes it possible to look up certain locations according to their characteristics. For example, the department_state table records which site a department is located in (in the new release, waiting for validation). That makes it possible to identify only locations in the tower or GWB, for example. This table is populated

The location_visit table is populated from a HL7 messages. For each Admission, Discharge or Transfer (ADT) event during a visit, a row is inserted. The location string is found in the HL7, and checked against star.location to get the corresponding location_id to insert as a foreign key in the location_visit table.

If the location string is not found onstar.location, a row is added to that table.

Problem (general description)

When a location string is not found on 'location', causing a row to be added to that table, no foreign keys are created. The image shows an example. The location_string ED^null^null (a very common string used all the time when patients first arrive in the ED) is not matched.

That means that this location is not identified with the relevant department.

It could also mean that, if any join (other than left join) on department, room or bed attributes is attempted, then contiguous location history over the course of a visit would not be retrieved.

For example, without a join to department_state (on Star) returns this:

and with a join returns this

Problem this causes for the patient flow predictor

We want to identify all patients in a location (eg all in ED or SDEC, or all inpatients in tower/GWB) at a point in time. The most efficient way to do this would be to search for all rows on location_visit with a foreign key matching with department_state for those departments (for example, all locations in the tower or GWB). We can't do this currently, because we miss certain patients. Instead, we use a long csv file with a list of ED and SDEC location strings. We do a look-up to find the location_ids associated with these strings before we start processing. This has a number of problems

• performance is very poor when you have to match against a very long list of location_ids
• we are not able to make use of the work recently done to add metadata to the department table
• a csv is a static list, which will get out of date