We’ve been wanting to quantify our patient experience, and we were thinking about having people sit at the door, etc and manually figure out how long patients have to wait before seeing the doctor, etc.
I figured OEMR is perfect for this, but I think patient state - arrived, in exam room, etc - is only stored as a single state in the tables: so there’s a single field in the table that says where the patient is in the process.
What if I added fields for different states, so an arrived field, checked in field, in exam room field, checked out field, etc. Then whenever our front desk changed the patient’s state, the timestamp for that is recorded. Then later we can run a simple query to figure out how long patients are waiting not to mention a whole bunch of other data that can be used for process engineering/improvement.
It seems simple enough…but am I missing anything? Will changing all this “break” anything? Will it dramatically increase the load time? Or is it a bigger challenge than I’m imagining?
Implementation:
Is this the correct file I should change? interface\main\calendar\add_edit_event.php
Any suggestions on how to go about where/how to start?
My quick thoughts here:
Probably easier to make a quick little mysql table with something like the following fields:
encounter_id
timestamp
status
Then place a function somewhere that simply dumps this information into the table. Then strategically place this function in places in code where the user changes the appt status. This should then collect all the information you want, which could then be analyzed via sql-queries and could even develop a report around it. If done well.
If lucky, may also be able to pull this kind of data out of the mysql ‘log’ table, although that could get a bit messy.
Quick question though: wouldn’t there be fewer entries/more efficient if i structured the table as encounter id, arrived, checked in, etc so that each encounter had one entry only rather than structuring it encounter id, status, timestamp where there would be multiple entries for each visit? Or maybe i’m missing someething and more entries is actually better?
Having a single column for status will provide a more flexible/extensible design.
This falls into the concept of a well normalized database schema http://en.wikipedia.org/wiki/Database_normalization
The need to predefine a column in your database for each “status” (arrived, in room 1, checked out, etc…) is bad design.
Fewer entries/rows != more efficient where a database is concerned when properly indexed.
Hi,
Making the encounter_id a key/index (not a primary key) should suffice well (would make the query go through a max of only several entries no matter how many million entries are in there).
-brady
Table Creation:
CREATE TABLE `avicenna_temr`.`appt_status_time` (
`sid` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`patient_id` BIGINT( 20 ) NOT NULL ,
`apptstatus` VARCHAR( 15 ) NOT NULL ,
`encounter_id` INT( 11 ) NOT NULL ,
`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = MYISAM
Do those seem appropriate?
I’m confused about where to put the function, however. Especially since I noticed that the encounter variable is declared after the UPDATE EVENTS section.
Thoughts on table:
1. remove patient_id (no need, since can get this from the forms table via the encounter_id)
2. add a key/index to the encounter_id
Function placement:
Call the function whenever ‘Save’ on the interface/main/calendar/add_edit_event.php script.
Thoughts on function:
1. Since you will call this every time your save on the add_edit_event.php, recommend the following:
-Check to see if an entry for this encounter_id already exist.
—If an entry for the encounter_id does not exist
-----then insert the new entry
—If an entry for the enounter_id does exist
-----then insert the new entry only if the appt status is different than the most recent entry
Above should then provide you nice clean entries for whenever the appt is created and whenever the appt status has been changed.
Your point about the encounter_id not being created until after appt has been created makes my original suggestion flawed. Instead of using the encounter_id above, will need to use an appt_id (i think the pc_eid from openemr_postcalendar_events should suffice). May get odd behavior during recurring appts, though.
For others that use the calendar recurring appt, doesn’t the current database structure cause bugs when setting appt statuses??