Medical Audit database (1 Viewer)

jdraw

Super Moderator
Staff member
Local time
Today, 15:14
Joined
Jan 23, 2006
Messages
15,364
I've created a draft data model for consideration/discussion. It may be useful, but some tables and relationships are more "guess" than fact. Once your details are compared/ worked against this draft, it could be adjusted or totally replaced.

Procedure: represents a reference table; a list of standard procedures (standard codes and names/descriptions)

Complication: represents a reference table; the CD table and any custom values you add to meet the scope of your requirement.
Note: there is only 1 Complication table, I have shown 3 in the model to show where it is used to supply values to related tables. (same concept as the UN(M49) table mentioned in #17).


 

Attachments

  • PatientComplicationsDraft.png
    PatientComplicationsDraft.png
    26.3 KB · Views: 377
Last edited:

MichaelHutton

Registered User.
Local time
Today, 19:14
Joined
Apr 12, 2015
Messages
11
Do you ever record procedures or Admissions without there being a complication to drive this? Do you ever have a complication that is attached to more than one procedure/admission?

A procedure will always be associated with an admission. A complication will always be associated with either a procedure or just an admission (with no procedure)

@The_doc_man You wouldn't really worry about a complication from a previous episode. On a new admission this complication just becomes their diagnosis.

At the moment I have patients ->admissions -> procedures -> complications. It is a way of us tracking the complications if they happen and know that we have discussed them at our meetings. If they don't happen we don't worry, but I do want to collect the data on patient admissions and procedures. Eventually, we can say for example 2% of hernias had a haematoma /bleed or something.

@jdraw - your model has three complication tables which I am not so sure I understand. Are they just the same table represented in the relationships window three times? Or are they really different? Uh no scratch that, just read it properly!
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:14
Joined
Jan 23, 2006
Messages
15,364
Based on your feedback, here is a revised model.

-a Patient must be admitted to have a Procedure
-a Patient may have 0,1 or many Complication(s) on Admission and/or after a Procedure

-there is only 1 Complication table that is used to identify a Complication

 

Attachments

  • PatientComplicationsDraftV01.png
    PatientComplicationsDraftV01.png
    26.1 KB · Views: 353

Mark_

Longboard on the internet
Local time
Today, 12:14
Joined
Sep 12, 2017
Messages
2,111
jdraw,
I am guessing you are including the patient ID in the grand children just to make it easy to run reports?
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:14
Joined
Jan 23, 2006
Messages
15,364
Yes. I was keeping the data for ease of reading/reviewing.
However, Patient/Admission could be non-identifying as below.
Additional fields/attributes required pending the OP's more detailed analysis.

 

Attachments

  • PatientComplicationsDraftV02.png
    PatientComplicationsDraftV02.png
    24.9 KB · Views: 350

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:14
Joined
Feb 28, 2001
Messages
26,999
@The_doc_man You wouldn't really worry about a complication from a previous episode. On a new admission this complication just becomes their diagnosis.

Wouldn't you at least want to remember when the complication was first observed? Yes, you would want to have that complication noted where it started.
 

Solo712

Registered User.
Local time
Today, 15:14
Joined
Oct 19, 2012
Messages
828
MichaelHutton said:
@The_doc_man You wouldn't really worry about a complication from a previous episode. On a new admission this complication just becomes their diagnosis.
Wouldn't you at least want to remember when the complication was first observed? Yes, you would want to have that complication noted where it started.

Doc, how do you understand the term "complication from a previous episode" and would this mumbo-jumbo include complications that are by nature iatrogenic ? Does "a new admission" mean "next visit" or are we actually talking about successive hospitalizations ? How does complication "become" diagnosis? Wouldn't there be somewhere, anywhere, in the database or connecting to some external Health Data Bank SOMETHING that every physician's office medical provider absolutely, without fail, has to have because without it we are not even entitled talk about medicine. It is called "medical (or patient's) chart" which organizes relevant personal medical data. To try to design something without basic grasp of common methods and data-gathering procedures in the field is a hopeless undertaking and waste of everyone's time. Pardon me for speaking plainly.

Best,
Jiri
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:14
Joined
Feb 19, 2002
Messages
42,972
jDraw - the tables should only have the FK to the immediate parent. They should not also include a FK to the admission.

Michael - I don't know if that will work as it is on my OneDrive. Hopefully. - this is a little concerning since you CANNOT share an Access database using OneDrive. If you need to have multiple people update data in the app, you need to be able to host the database on a LAN or use Citrix or RDP. OneDrive would work If and only If, ONLY YOU make modifications. All other users would need to be read only.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:14
Joined
Feb 28, 2001
Messages
26,999
Part of the problem I am seeing is that they have been keeping records in a haphazard way and need to formalize it, but since they have not formalized it, the OP is having trouble putting everything in its place. At least, that's my take. Which is why I put in my comments on the design process.

One of the side-effects of haphazard data gathering is that it might appear that they are ignoring things. But maybe the REAL problem is that without a more formal method, they have not asked the right questions about their data flow model yet. They aren't ignoring something so much as they are unaware of something in the process.

A theme I see in this thread is that they are looking for trends and long-term statistics and possible spikes in certain events. In other words, time-correlated events, so they can tell the difference between a "normal" rate of complications and a sudden rate jump in some type of complication. Knowing that a particular event occurred during a prior admission vs. a more recent one helps them figure out if the complication WAS iatrogenic instead of ideopathic.

Does "a new admission" mean "next visit" or are we actually talking about successive hospitalizations ?

Depends on their business rules. I have no idea.

To try to design something without basic grasp of common methods and data-gathering procedures in the field is a hopeless undertaking and waste of everyone's time. Pardon me for speaking plainly.

Jiri, was that aimed at me or the OP? Saying that something is hopeless doesn't help the problem and could be taken as disparaging.

I am not clear here whether their facility is for the USA equivalent of a general clinic with primary-care physicians and outpatient specialists, or whether this is a specialty surgical clinic for outpatient and short-stay patients. In the former case, the patient's medical chart is CLEARLY much more important. In the latter case, I'm not so sure. So your comments about medical charts might or might not be as relevant as you think.

Based on what I've read, I see this as the latter of those two, where they are looking for problems that are at least potentially caused by errors in the clinic's operational procedures. But they need to take their data in a way that conforms to a UK standard for same, at least a little bit, hence their "standardized complication charts."

And as for designing something without a basic grasp of common methods... Please don't forget that Access is SO inviting to beginners - who NECESSARILY will not know those methods of which you speak. Our OP commented in passing that he thought Access would help him more, but now he realizes that he is still responsible for driving the process. So yes, they have a learning curve. So what? We ALL started with one. And everybody's got to start sometime.
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:14
Joined
Jan 23, 2006
Messages
15,364
I agree with Doc. We haven't really seen any details, nor data in context. In my view there's a lot of guessing going on. We have not seen the ultimate use of this database with respect to outputs. The "business facts/rules" have yet to be described and tested. The purpose of the models I have submitted is simply to flesh out more of this "hidden/uncertain" information.

It's possible that Patient may not be required in any reporting. The OP may be looking specifically for Complications related to Admission, and Complications related to Procedures only. Much like many statistical processes where only summary data is presented (no identification of individuals).

I think it comes down to more information or examples of what is the true requirement. The OP knows the requirement(s) better than readers, but is having difficulty describing it. We can make guesses that he can adjust, refute, accept with conditions etc, but in the end we're only looking and guessing at small pieces of the "elephant".

 

Attachments

  • PatientComplicationsDraftV03.png
    PatientComplicationsDraftV03.png
    23.7 KB · Views: 354
Last edited:

MichaelHutton

Registered User.
Local time
Today, 19:14
Joined
Apr 12, 2015
Messages
11
Firstly, thank you for all for getting involved! I am trying to understand some of the ways you are trying to lead me!

It is difficult to reply to all individually but it seems there is a common thread of '"what is this for" and "business model". I'll try and explain. The real issue we have where I work in the UK is that we need to keep track of complications we can cause for procedures we undertake in the hospital. We do have a national database of HES data (Hospital Episode Statistics) which is an incredible resource. However, the data can be slightly misleading in some ways because of poor (and I say that recognising it is a HUGE job) coding. Coding is often done very expertly by people sitting in a back office and going through the notes we have written. However, we find that sometimes this doesn't capture the data in a way we can fully interpret. The other drive is for us to collect information we can show about our episodes of care and present them at monthly Audit meetings. We already do this but the data is collected in a massive excel sheet which ends up getting used by multiple people and the data gets messed up on a regular basis by the thing sitting between the chair and the keyboard. Not a lot of people have good computer skills. I don't either but have some....

Oh, and the database is not on my onedrive - it is on a hospital network. It was only a picture I was trying to link to earlier. I have just today been able to link a query to our patient database so that we can automatically search for patients form their CRN (Clinical Record Number). This is a huge step in trying to reduce the data entry for the user.

Therefore, I want to collect information on patient episodes/. The hierarchy has been discussed and (I'm still struggling a bit to understand the relationships posted above but am slowly getting the idea).

I'm slowly absorbing all the information posted above. So, to reiterate, thank you for helping. I'm sorry that I can't really give more of an idea at the moment....

Michael
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:14
Joined
Feb 28, 2001
Messages
26,999
Good luck, Michael. Remember, build your database as a model of reality. Never let the tail wag the dog.
 

Users who are viewing this thread

Top Bottom