recommend if adding a field to patient_data to add to end of structure to avoid what may be issue with current sql’s.
I’m going to start a new table called patient_status. Patient_data is large enough.
Archiving has a benefit for sure, it makes sense to push out inactive records. For instance, a patient that is deceased for a year does not need to be taking up space in the DB. It has the benefits of faster queries and leaner results in general. I like it.
@juggernautsei I’m leery of adding a ew table for statuses. Could really complicate many queries. If you decide to go that route I’d suggest investing heavily in finding the most optimized SQL first. Probably include some level of join or sub-select’ing
After taking time to think about adding a table and all the complications it could add in doing queries. It would be simpler to add a column to the end of patient_data. It would be nice to have a dBA to weigh in on this topic.
Thinking out loud you may want to consider how this may tie in with institutional settings. If adding an indication for patient status you may want to make it a pull down populated from list so that other patient status can be used besides inactive. Transfer, Deceased, Inactive and on. come to mind. Depending if deceased is actually used anywhere in reports you could rob date deceased and reason and make them status date and status reason adding a pull down for status.
That is why I was thinking for a new table because there are other statuses that can be set for patients. But I understand what you are saying and that is why I started this conversation back so that it can tie into more than just the individual piece that I have built.
Keep the ideas coming!
I agree that soon we are going to need a companion relational table for patient data especially concerning institutional but I fear however you introduce an inactive status to patients then does that mean we have to follow through and look at patient status for all our reports? As sure as day when a user see that a patient can be made inactive they will expect to not see that patient in reporting. Until we can get a handle on what we need for institutional, I would try to the last not to touch demographic’s. Maybe there is a way you could setup a rule of what would constitute an inactive patient for your needs. Maybe in globals
For last few years we have used series of reports and sql updates to identify and archive patient records. While processor power mitigates impact on database performance by keeping inactive patient records , these records do impact clinical measurements and related actions such as follow ups.
If there is any interest in this area and the objectives are known, we can share our experience and few tools.