Medical Audit database (1 Viewer)

Hi all,

I'm a relative newbie at database design and I just can't seem to get my head around how I should go about designing a database efficiently. I don't want to make any design 'clangers'. I am trying to design a custom database for us to collect audit information for our medical group. I realise I might be reinventing the wheel but maybe if I can just re-align it instead....

My main tables are:

1. tPatients - NHSNumber (primary key), names etc.....
2. tAdmissions - AdmissionID (PK), fkNHSNumber, dates, diagnosis etc
3. tProcedures - ProcedureID (PK), fkAdmissionID, dates and details etc

I have made a one to many relationship for the tPatients to tAdmissions using the NHSNumber to fkNHSNumber and then another one to many relationship from the tAdmissions to the tProcedures using the AdmissionID to fkAdmissionID. As I understand it that seems to be the way I should do it being that each patient can have multiple admissions and procedures are associated with an admission. It seems fine (I think).

My issue comes when I want to add a complication table, tComplications, but I want a complication to be associated with either an admission or a procedure (the patient might not have had a procedure) but I just can't get my head around how I should design it. This is where I get a bit stuck and although I've read a bit my head is swirling. I just want to get it right the first time and not break any rules so to speak.

tComplications has two foreign keys to each table? What sort of relationship should I enforce? Can one table reference two different tables? Should I just use a query? Should I have three cascading tables?

You can see me going round in circles. If anyone understands what I am trying to get at I would be most grateful if they could stop me spinning and head me off in the right direction..... :banghead:

Michael

Do you have a clear description of your requirements? That's typically the best place to start.
Here's a link with info that may be useful to you. I recommend that you work through 1 or 2 of the tutorials from RogersAccessLibrary that are identified on the linked page.

Good luck

Agree with jdraw. I'd really think about what a complication is and what data you are capturing.

Usually when a thing can be attached at 2 different levels (Admissions > Procedures), I attach it to the lower level (Procedures) and force the higher level (Admissions) to have a lower level. So, if someone had just an admission and no real procedure, I would still force them to have a procedure record, that way I could attach a complication to it and everything would roll up correctly.

Michael,

I'm going to give you some up-front and very pragmatic design advice. (Not theoretical at all - pure pragmatism.)

#1 - If you can't do it on paper, you can't do it in Access.

What this means is that if you are still struggling with how things go together, your "blueprint" is not ready for implementation. Not to say that you can't play with table and relationship designs, but they will not be "ready for prime time." You have to have designs on paper and think through them at a procedural level.

Why is this? Because Access is dumber than a box of rocks. It knows NOTHING about what you are doing. YOU are the subject matter expert.

What I sometimes advise is to invest in a really high-tech design kit if you have a way to do this. The kit consists of a bunch of sticky notepads, a dry-erase board & markers & erasers, and a digital camera (includes smart phone) so you can take pictures and store them somewhere for contemplation. If you have multi-color notepads, pick colors for each potential table and use the sticky notes to represent entries in the table. Then draw lines between the collections of records representing each table so that you can see whether a direct or indirect relationship makes sense.

While designing this, consider the next rule:

#2 - Access won't tell you anything you didn't tell it first, or at least tell it how to get or compute that thing.

Which is to say, part of your design process will include detailed OUTPUT requirements. So starting from those requirements, work backwards through your design to assure that everything you wanted in the output is directly or computationally available from inputs.

Now, let's look at your model. And I'm saying it this way because what you are doing is you are building a logical/programming model of your business data flow. So it often helps to recognize and carefully examine "the real world" because if your model doesn't match the real world, your model (database) is wrong. This is the ultimate metric by which to measure your success.

You have
- Patients
- Admissions
- Procedures
- Complications

One would presume that you have many patients. If your patients are like any other people, they stay with the medical group that helps them. So you will get repeat business. Which means repeat admissions. So obviously you will have a many-to-one relationship between patients (one-side) and admissions (many-side).

Typically, patients put stuff off and then try to get more than one thing done at once. So ... if you see this, then you have another many-to-one relationship. Here, it is one admission can potentially lead to multiple procedures. Here, you need to consider how you count something. Let's consider a dermatological issue where a person has multiple actinic keratoses, a precursor of skin cancer, and wants the doctor to remove them. Ask yourself whether that is one procedure with multiple action sites or multiple procedures, each on a single action site. This is where your business model kicks in and provides answers.

Now, complications arise. (Don't they ALWAYS?) The question is, how would your business count these ON PAPER? Is it a complication of a procedure? Then you have a many-to-one relationship with many complications to one procedure. But remember, you are modeling this on your business flow. If you can't answer this, perhaps it is because your business flow doesn't answer this question clearly.

This leads to that rarely used rule,

#3 - If you don't have a good answer even after study, the problem might not be within you. Always consider the source of the problem.

If you cannot get a good answer regarding this relationship, you need to get a consensus of the people in this business because presumably this database you are building will help THEM with some records-keeping issues. And it IS possible that they never really thought about this either.

Now, one final point because I know this will be a lot to digest. We talk about using a one-to-many or many-to-one relationship, also called parent-child relationship, even though in the case of your complications history, there might be nothing to record. This is OK. We use the one/many relationship when we don't know exactly how many entries will be on the "many" side and that explicitly includes NONE. A one/one relationship is EXCEEDINGLY rare. So as part of your design, it would OK (actually, from the patient's point of view, GREAT) that there were no complications to be recorded.

Good luck and remember to take the time up front to get the design matched to your business model. Because the business data flow is always right.

Wow, thank you very much for the replies. I appreciate the help. I will look at the link @jdraw, and I appreciate now @thedocman what you are saying about me being the designer. I was hoping that access would do a bit of magic re the data but I realise that is not the case so much.

@plog, I had thought of that, attaching it to the lower one and forcing a dummy procedure to be made if it was for an admission. I suppose it would be relatively easy to make a query on this to filter the results. I will probably go with this. I was just so wracked with guilt about making big mistakes in the design stage but I think this makes sense.

Thanks again for the help.

Michael

Another option is to connect the complication directly to the admission but then allow ONE procedure to be referenced in the complication table.

The key is understanding the relationship between complications and procedures. Looking at the requested reporting on complications may help you to understand the relationship.

It doesn't make sense to replicate the same complication under each procedure deemed contributory. It makes more sense to do what I suggested above and connect to only a single procedure OR if you really need to connect to multiple procedures, create a junction table between procedures and complications. That allows you to define the relationship without duplicating anything.

I'm pretty certain the relationship is NOT Admission-->Procedure-->Complication UNLESS there can be only ONE procedure for any given complication.

"I'm pretty certain the relationship is NOT Admission-->Procedure-->Complication UNLESS there can be only ONE procedure for any given complication."

The way I see it I that there can be multiple complications per procedure (there are when I operate) but there can also be multiple complications per admission (even when I haven't touched the patient) which don't involve a procedure. I am not sure I am capable of doing a 'join' at the moment so I think that having a dummy procedure to attach any complication associated with the admission would be best (for me)...

Michael

For myself, I'd also check with who ever is in charge of your audits. See if there are any cases where a "Complication" would need to link DIRECTLY to a patient but is not connected to a given "Admission" or "Procedure".

I'd also clarify what types of "Complications" there will be as I can easily see something like this encompassing not only billing issues but complaints against staff if your not overly clear on scope early.

Hi Mark,

Luckily in the UK NHS there is no billing issues... or unluckily as I certainly don't get the kind of salary as I would in the US! It is an 'internal' database we keep (the current horribleness is a massive excel sheet and you can just imagine the horrendous mess that is) to make sure we can agree with the nationally collected complication data skimmed off the national HES (Hospital Episode Statistics) data which are notoriously inaccurate....

We use the Clavien-Dindo complication grading system which is pretty well accepted at the moment. This database also will help us to make sure we discuss all the relevant cases in our month M&M meetings.

Michael

I found this re Clavien-Dindo Complication classification.
The reference seems to imply complication of surgery, but you know the classification and its applicability.
Perhaps you could describe a couple of situations where a classification is assigned and recorded. The context may help us to understand the requirements and design issues.

Not familiar enough with medical systems (other than having to go through them as a patient on US Medicare+supplemental) to understand the distinction in complications, but it makes me ask this question: Could we perhaps be missing a component here?

If a patient comes to you and is admitted, you don't start with a procedure. You start with an examination and identification of symptoms. (Or if you don't, then the referring physician has done this.) Is it perhaps that the patient has SYMPTOMS for which there are complications and then, can it be that the procedure can encounter a different class of complications? And in that case, do the "symptom complications" overlap the "procedure complications"?

I'm thinking that the complications actually refer to the patient but they may manifest in different ways - during examination or during procedure, for example. Does that make sense in your context?

@jdraw and @The_doc_man - Yes, the Clavien-Dindo classification was originally described for post-operative complications. However, a complication may be one not related to a procedure. For example, a patient gets admitted with a bleeding bowel (PR bleed) but then let's say has a heart attack or a stroke, which is a recognised complication, rare thankfully, but it can happen. We could say that there has been no procedure but we could say that a blood transfusion (which requires consent) was a procedure. We definitely should be collecting this information. How else would we know if one unit is performing similarly to another and how would we detect outliers in patient care? In effect the CD classification is not really the important point it is more that we collect the information of the complication that occurred. To be strict, you are right, we shouldn't classify that as a CD grade. I'm sure there are other ways of classifying non-operative complications. I must admit of not investigating that yet as I don't normally get involved with that.

I think the essential issue is that a complication, as I understand it, is a deviation of the normal expected treatment pathway. They may be well recognised and described complication but if it happens we definitely should record them. There are interesting aspects about this data collection as well. Some people would not say a UTI (urinary tract infection) is a complication of surgery as they happen often but it is good to collect this data so as to know what the rate actually is. This sort of data also helps to be a 'index' of the other data being collected. Let's say a paper is published which shows that a units UTI rate was 0% but they are reporting let's say bowel resections we would be able to ask whether we would trust the other statistics in their paper if it is recognised that most other units have a UTI rate of 2% (I have made this all up, as they say 86.4% of all statistics are made up on the spot!). I'll try and post the relationship window I have at the moment. Hold on...

I don't know if that will work as it is on my OneDrive. Hopefully.

It is the basic tables I was talking about. The tables are not complete yet it was more of the links I am interested in.

ah. I can't post links to images yet...

This could get complex (wait... COULD?) but maybe the issue is that ALL complications apply to the patient BUT you can have a code in the complication that says it is pursuant to an admission or a procedure or something else. And if so, you could then link the PK of the admission or procedure to the complication. And it would ALWAYS be linked to the patient because complications belong to the patient.

The query for this would not be pretty, but if you have a fixed-position indicator of the phase in which you discovered the complication, then you can select for the type and put it where it belongs in your report and/or statistics.

IF this is the road you take, you MUST SCRUPULOUSLY AVOID designing a linkage "fork". What I mean by that is lets say you list a fainting episode during the procedure. The complication links to the patient. But you CAN in this case link the procedure to the complication as well. Since the procedure ALSO links to the patient, you could VERIFY that the procedure and complication links match. BUT if you ever want to get the patient ID, you NEVER take the path of "complication" > "procedure" (via FK) > "patient" (via FK). That is because there is the direct "complication" > "patient" (via FK) path. AND it is possible that if you have formal linkages to the patient, you might have to write your queries to suppress the less direct linkage. (You can do this in the query design grid, in the upper area that shows the tables and linkages.)

"This could get complex (wait... COULD?)". Ha ha. I knew I was in trouble!

I must admit to thinking that I need to follow the KISS principle here (Keep It Simple Stupid) and do just what I can do. So, being a not so very good database programmer I think I like the idea of inserting a dummy/null procedure which will basically just indicate that it is related to the admission. The user will never have to see this dummy procedure. Only I would know about it. The complication will still be linked to the patient via the other tables (as I see it).


"The query for this would not be pretty". Statements like that reaffirm it for me! I want to get something simple up and running. I do envisage tinkering with it and adding more as time goes by but I think I should walk before I can run.

Michael,

Let me make an analogy that may help with context and your intended use of CD classification.

Consider a business that buys materials from different countries, uses some or all of them to build some products and ships said products to different destinations (hypothetical, but for demonstration).
Let's say CountryOfOrigin is recorded with the purchase of materials. And also CountryOfFinalDisposition is recorded with Shipment. And to further "complicate" this that a Shipment may have many PortsOfCall during the delivery of/shipment of a Product(s). Let's agree that each PortOfCall is located in a country. So, since Country is used in various "parts" of this business, it would be a good practice to have some standard system for identifying countries. For example,United Nations M49 (picked via Google) could be used. So in this business CountryOfOrigin, CountryOfFinalDisposition and each PortOfCall get their appropriate values from the UN(M49) table.

In your set up, it seems you are intending to use the CD classification as your standard for identifying a complication(s) regardless of location or timing of the complication. In theory, I see no issue with that ---analogous to using the UN(M49) country code. You would note that you are not restricting your usage to "surgery/surgical procedures".

Do you have a list of standard "procedures"?

I suggest you describe a few scenarios where a complication would/could arise, or has arisen in the past and recorded in your records. Just trying to flesh out the requirement in more detail.

I'm quite sure that the business rules/facts will evolve and the "relationships" identified as details are reviewed.

Do you have a list of the kinds of reports/statistics you want to derive from the collected data? Some examples would be helpful. Outputs are a determining factor on inputs and/or calculations and usually have bearing on "related information".

Good luck.

To touch on what Doc posted...
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?

If you are really tracking complications by patient then your database may be far simpler than you've laid it out.

Patient as parent, complication as child.

Admission and procedure then become children of the complication (and optional, as far as the data is concerned) rather than the other way around.

I agree Mark. It seems, during reading the thread, that we're talking about a "Complications by Patient" database. Just hoping the details would confirm/clarify the ultimate purpose of this proposed database.

Can a patient have a procedure without being admitted? Is this within scope of your project?

The way I see this, you have a patient. The rules of database normalization say that you must associate things where they belong.

A patient can have an admission. So admissions "belong" to patients. That means your admissions list has an FK for the patient who was admitted. (And a date/time, since one patient could clearly have different admissions and you need a way to differentiate between the two admissions.)

A patient can have a procedure. So procedures "belong" to patients. That means your procedure records have an FK to the patient. BUT it is possible that you would want to ALSO link the procedure to a specific admission. So there would be another FK from the procedure to the admission. This suggests that procedures are grand-children of the patient and children of the admission entry. But that depends on your business rules.

A patient can experience/exhibit a complication. So complications "belong" to patients. That means your complications entries have an FK to patients. But you seem to allow for the complication to be detected at different stages. You would have some kind of code field to represent whether the complication walked in the door with the patient (previously diagnosed in another admission) or whether it occurred during the procedure (say, a case of syncope when you stuck the patient with the horse needle). And you could thus justify having an FK from the complication to the admission or the procedure. Doesn't even have to be the same field (in fact, probably shouldn't be the same field).

So you have one base table (patients) and three related tables. Admissions - a child table of patients; Procedures - a child of admissions and a grandchild of patients; and Complications - a child of patients and the red-headed step-child of either admissions or procedures.

The other tables that you use to encode complications are translation tables and don't make a huge difference in anything. At worst you need a JOIN of the CD code table to the complication table on the CD code field. Just remember that you can build complex SELECT-style queries and use them to power anything that takes/requires a recordset.