Help with Database Design: Cross Post for Medical Database Build

[jrichardson10]

Breaking news, this just in:

All our beloved Sesame Street characters have fallen into the throes of opioid addiction. But not to worry, the Sesame County Public Health Department is on the case! Using a new research study to assess their approach, the SCPHD is helping our lovely friends stop their injection drug use and achieve sobriety and maintain their health.

I am the Data Manager for the SCPHD and I need your help to construct a database that will help me keep up with the following:

Patient Info
Contact info
Insurance and Prior Authorizations
Referrals
Substance Use
Treatments and Medications
Blood Sample Results
Urine Drug Screen Results
Study Enrollment
Random Pill Count Results

Right now these are ten tables, but I need some help getting the design to actually work. I'm inexperienced with Access, and what experience I do have is dated. I need to be able to look up existing patients in the database and add new patients to it. I'd like to connect everything so I can add or just autofill forms, but while I know what I want to do, I don't know how to do it. I'm happy to provide details, or post in a more relevant thread. Your favorite characters need our help!

If you have any suggestions on where to get started and how to appropriately build tables and relationships, I'm all ears. Thanks!

[orange]

I suggest you read through this post and related links to get familiar with the overall process of database design.
Review the questions and responses as the thread evolves.

[jrichardson10]

Hi orange.

I read the whole thread, and many of the articles referenced in there. It appears I am also in the "Knows Nothing" camp. I've run through the logic of my would-be database several times.

I suppose I should start at the beginning. For every patient I have, I need to also know about them and how they found me. So, the relevant tables are tblPatients, tblDUR, and tblReferrals. Though I read that my first step is not designing a form, I need to be able to both enter new patients and autofill a form with existing patient data. I then need to refine into further data for those enrolled in the study with tblAUBUPart. I suppose my questions boils down to is my logic modelling solid for that section (shown below)?



I need to know how to contact each patient, their MRN, who referred them and when, and I need to know what substances they report using. If they're enrolled, I need to list their enrollment date, their status in the study (active or not), their research ID, and the dates of all their research visits. I think my logic is sound. Does all that make sense?

[aytee111]

Figure out what information is stored once only, such as patient name, and which information is repeated, each one of their research dates. One-to-one joins are nonsensical, keep it all on one table. Why the one-to-many referral table? And what is the first/last name on that table, wouldn't it belong to and MRN? Methinks that can be on the patients table.

And all those dates - FU/CA 6, 12, 18 - will there be others in the future, such as 24? In which case it needs to be a continuous table, FU and CA and date, with a master table containing the 6 and 12...

You're right, do not be concerned with form entry right now, purely what you want to carry on the database.

And naming conventions - when in table design mode you are a developer, not a user, so keep it technical. Names should not contain spaces or any special characters, only an underscore. And keep tables simple, set the field sizes correctly, no formatting, no calculated fields, and no lookups. All of those can come later.

[Micron]

jrichardson10:
Where's the cross post? We'd like to know what has been suggested elsewhere so as to not post repeated suggestions.

Oh my! You said you reviewed all the links yet you have spaces in object names and worse, characters like plus signs, repeated values, etc. No disrespect intended, but what are we to conclude? You missed that topic? Disagree with the information? Can't easily be convinced to follow suggestions or directions? I'm just confused and concerned. Try to understand that we're all volunteers here, but I will speak for my own concerns and ask if there's any reason to believe time invested here might be for naught. Don't be put off by this. There are people in this forum who will vouch that I have been known to devote MANY hours to helping solve a design problem - if there's cooperation.

Perhaps you can enlighten us as to what some of the terminology is so as to facilitate suggestions. I have no idea what MRN or AUBU is, thus am clueless as to where it belongs. Maybe revisit the notion of entity relationships to help yourself here. Not sure, but I don't think you've quite got that part. F'rinstance, I suspect research stuff doesn't belong in tblPatients, rather PatientID belongs in research. If a patient, who should only be in tblPatients once, is involved in multiple research initiatives, then what? Create a column in tblPatients for each initiative? Absolutely not. Rather than create a field in patients to link to research, research table should contain the patient id for as many initiatives that they're involved in. While you're at it, the common fields amongst tables should bear some similarity. When you're looking at code, query design or other such things, you'll find yourself wondering what the heck field XYZ is, where it comes from and what it's supposed to be related to. An example of similarity might be PatientID in tblPatients and PatientID_FK in tblReferrals to remind you that the latter is a primary key somewhere else, and the first part of the name should provide the clue. Not being one to use the underscore, I'd be attempted to write fkPatientID or maybe PatientIdFk. And do you have lookup tables for aspects such as drug names rather than relying on accurate keyboarding each time one needs to be referenced or written to a table?

To facilitate assisting, a bit more about the nature of the business would be helpful.

[ssanfu]

tblDUR (DUR = drug use report???)

The drug name (Amphetamine, Methamphetamine, Benzodiazepine, ect) is DATA, shouldn't be field names. (What does the + after the drug name signify?)

As aytee111 said, "will there be others in the future"???
If you use the names of the drugs as field names, what happens if you decide to add 1 or more new drugs? You have to modify the table, queries, forms,reports and code.
The drug types (names) will be in another table (maybe named tblDrugs); there will be a FK field in tblDUR related to the Drug table PK field.



Edit: Micron beat me posting... again! - and, yes, Micron (one among many others) does put in a lot of time on answers for those that do want to learn......

[jrichardson10]

All, thanks for getting back to me. I'm going to hit all the replies in one go, so bear with me.

ayetee111:
So, the one to one join is really a product of Access not allowing me to generate a one to many join. Because not all my patients receiving treatment are participants in the research study, I put that info in a new table to avoid blank data spaces. Is that not the best approach? So like of my 30 patients (I'm making this up), only 21 are enrolled in the study and have study related data. So to avoid 9 lines of holes, I made a new table. Also, being new to databases, I put first/last name on each table because each report we get in has that info and I didn't know how to generate a form with draws from multiple tables. (I'm really new to this.) It sounds like I should be able to disregard that by filling forms using multiple tables? Yes?

The follow up dates are only those six, after that the participant is done with the research study.

ayetee111 and Micron:
So my hesitation with using the developer language is that when I leave this position, the database should be self sufficient and require no tweaking, but in the event it does, there likely won't be anyone with the technical know-how to read the developer language. So I'm trying to stick with more common language, but I can be convinced otherwise if I can be sure that it won't be a whole giant learning curve for anyone who replaces me. This is only a two or three year study, but I like to contingency plan.

Micron:
I totally agree with your concerns, and I didn't change things just yet because I don't know how well someone else from my research could do with it. Like we do everything on paper right now, that's our level of technical sophistication. I don't mean to put anyone off by seeming stubborn or unwilling to learn, but I'm just nervous about unveiling this to the research team and eventually leaving them with it. Because I suspect one or more of them would be well-intentioned but could cause the whole thing to implode. I also searched for the cross post again. I'm going to get back to you on that because I can't find it yet. That's the level of technologically challenged I'm working with. Will find where it is or where it has been deleted.

So MRN is medical record number, it's unique to every patient. AUBU is the sponsor organization for the research. So AUBUPart refers to participation in the AUBU research study. The plus signs behind the drug names are for a yes/no data field to indicate whether or not a person reports using that drug and will later be repeated in a different table to indicate what their urine drug screen (UDS) shows as being present in the sample.

The research info in the patient table was to indicate whether or not that patient was enrolled in the research study. Those that are have a unique participant ID as well, which is how they are labeled in the tblAUBUPart as the primary key. I don't want to say that this will be the only research initiative tracked here, but I suspect that will be sprung on me later, but for now it's just the one. As for the lookup tables, no I don't because I don't know how to make those. I need to find a good thread to read up on those. I'm so sorry I'm a total fail at this.

As to the general nature of the database build, I am actually a data manager for a research study operating out of a treatment clinic. There are like 20 data managers and only 3 of us throughout the country have this particular kind of data to work with and the other two have 20+ years experience so they have integrated their stuff already with their EHR (electronic healthcare records) but they use a different system than I do. (Kill me.) We need a way to track all our patients and keep straight their research participation. So all our patients are receiving some kind of treatment of opioid use, dosages and types may vary a little bit. Many of them, but not all, are enrolled in the research study I work for. But, because research enrollment has no bearing on treatment, it's easier for my clinicians to see it all in one place. We have patient medical records, info about their referrals, info about their study participation, info on what substances they report using, info on attempts/successes with contacting them (in person and over the phone), info about their insurances, their medications, random pill counts, regular urine drug screens, and blood samples to monitor certain levels we care about. Hopefully that clears up some of the fog? Because of HIPAA I can't give too many specifics, but that's why I picked muppets as my fake patient base.

ssanfu:
Yes, actually. What a patient reports to us as something they have or have not used since their last visit. They are fields with plus signs to indicated whether they have used (+) or have not used (-) that substance. So it's a yes/no field. I suspect there's a better way but I just don't know it yet. So if it's data, that might be simple if I can multiselect from a list, but because many of our patients use more than one substance, I split them up so it's like the physical paperwork we have. (Ease of us for non-technical clinicians.)

No, no more than just those dates. As for drugs, I don't think we'll add more, but I'm going to go knock on some wood for that. I think I understand what you're suggesting, but maybe not. I had the logic that tblDUR would basically a report card that gets filled out every patient visit with yes I've used that substance or no I have not used that substance. So basically I could add a substance to a theoretical drop down list or something similar to have a list of options from which to select one or more?

Thank you all for getting back to me. This project may actually kill me, but I promised I would try.

[orange]

jrichardson10,

I am not in the Health info business, but I have seen many threads/posts that suggest that MS Access is NOT HiPAA compliant.
I did find this overview that may guide you. And this one.

I really think you need to do more analysis on what you want to automate and who and how will access this proposed database.
Things like "Because not all my patients receiving treatment are participants in the research study, I put that info in a new table to avoid blank data spaces." hint to design but is not really a business fact(in my view).

Since you do everything on paper at the moment, do you have documented procedures instructing users on the current "system". Often review of current system will identify the major pieces required of the new system, and identify those current deficiencies which must be redesigned/rethought and/or rebuilt in the proposed system. Identify all the pieces, identify priorities and make a plan. As you work through your project, update your plan and communicate with the parties involved. Do not assume you have it all correct and complete -plant some "obvious misinformation" and let users/managers/other highlight your mistake --surprising how much bonding can occur. Also, question what users/managers/others may tell you --they see things from their perspective - which can be limited in many cases. So while looking at the overall project, listen to others. They can be your best allies if they have a share in the outcome.

Don't underestimate the importance of requirements and details. If you have a new system, and that new system uses (vba code and procedures)

there likely won't be anyone with the technical know-how to read the developer language

. Possibly, but if you have that knowledge or suspicion in advance, then be proactive and include documentation on all aspects of the new system (management overview, training sessions (even youtube or related videos), user manual, technical manual including design details and all system , operations manual including backup procedures........)

Consider this situation, suppose someone in a different business wanted you to build her/him a database. What would you need from them in order to proceed, and to ensure they got a database that matched their needs.That's the detail I'm talking about. You can work from a list: who, what, when, where, how, how much or how often?

Bottom line here is to scope out the whole project. If you're not sure how to do it or what is involved--use google and find some samples.

My gut feel is you still haven't clearly identified what you intend to do, but you know your set up and expectations better than anyone. The purpose of the advice and suggestions given by forum participants is for you to be aware of your situation and to offer suggestions and/or options that might affect your project.

I repeat --don't be too quick to jump into physical Access.

Good luck with your project.

[ssanfu]

@jrichardson10,

Any chance you would post your dB? Don't need real patient data, use the muppets as research examples. Easier to see what you have so far.


Best is to draw an ERD on paper/whiteboard, cardboard/etc first to design tables/relationships, then adding data to see if you the structures/relationships wok ,THEN create the tables in Access. Saves a lot of typing time.....

[jrichardson10]

Hi again orange and ssanfu!

What a weekend.

To address the HIPAA issue, this is used only in house and is accessible only from a HIPAA compliant server, so no worries on that. Once it's built, I'll move it back there and then no one can get to it or even see it's there unless they have the special access.


So I am still getting little tidbits from my supervisors as they are fed changes from theirs, but I have the basic purposes of the database down. I've yet to coerce my supervising physician into sitting down and seeing the database as is, but I hold out hope I'll get there. In answer to your question @ssanfu, yes, I can do that. Right now, I'm not ready to fight with tblMedLog or tblPAs as we are getting new information on what those need to be in the next couple days, and tblContactAttempts is also another one I probably should bang my head against the wall a bit more on. I've attached the file as is, and like I've said before, I'm heinously new to this, so feel free to rip it to shreds if necessary. I'll take all the help I can get.

[orange]

I looked at the database relationships and table definitions.

Here is a png of your relationships with tables extended to show all fields. It's easier for readers if you show all fields in all tables.
You have described many of the fields in your tables. You have not described what each table represents. The field names to a non-medical person/worker seem to be heavy into jargon-ese, but you know your audience better than readers do.

It appears to me that many of your boolean fields (tblIUDs) are what you might display on a form, but I would not store data in that manner. Normally you would have a table of patient orders/tests (whatever this table represents) with an ID, PatientID,testID and a related table "tests" which would include "testId","testName".



I ran a routine against your tables to identify field info, and attached it below.

table_name	field_name	field_description	data_type	length
tblAUBUPart	PartID	DEC assigned Participant ID	Long	4
tblAUBUPart	Status	Enrollment status: Active, LTF, Not Clinically indicated, may need to add a couple more...	Text	255
tblAUBUPart	dTeEnroll	Date of enrollment, must match with DEC website	Date	8
tblAUBUPart	dTeBaseline	Date of baseline survey, must match with DEC and RedCap	Date	8
tblAUBUPart	dTeFU6	Date of 6 month follow up survey, must match with DEC BUT, this and all following dates may be blank because they were not actually done (patient lost to followup, died, pulled out of study, many reasons)	Date	8
tblAUBUPart	dTeCA6	Date of 6 month Chart Abstraction, must match with DEC	Date	8
tblAUBUPart	dTeFU12	Date of 12 month followup survey, must match with DEC	Date	8
tblAUBUPart	dTeCA12	Date of 12 month Chart Abstraction, must match with DEC	Date	8
tblAUBUPart	dTeFU18	Date of 18 month follow up survey, must match DEC	Date	8
tblAUBUPart	dTECA18	Date of 18 month Chart Abstraction, must match DEC	Date	8
tblBloodTests	BloodID		Long	4
tblBloodTests	dTeSamp		Date	8
tblBloodTests	MRN		Long	4
tblBloodTests	CD4	Has to be a number	Long	4
tblBloodTests	PerCD4	% but not a calculation, it's a direct entry from medical records	Long	4
tblBloodTests	VL	can be a number or it could be a Not detected (ND) or a QNS (quantity not sufficient, also noted <20), which is usually the case.	Text	255
tblContactAttempts	ContactID		Long	4
tblContactAttempts	dTeContact	do I care about time?	Date	8
tblContactAttempts	MRN		Long	4
tblContactAttempts	Provider/Staff	who tried or did contact patient	Text	255
tblContactAttempts	Type	Phone, in person, ?	Text	255
tblContactAttempts	Result/Notes	How it turned out, free entry here	Memo	0
tblDUR	Substance Report ID		Long	4
tblDUR	MRN		Long	4
tblDUR	dTeReport		Date	8
tblDUR	AMP		Boolean	1
tblDUR	METH		Boolean	1
tblDUR	BZO		Boolean	1
tblDUR	THC		Boolean	1
tblDUR	COC		Boolean	1
tblDUR	OPI		Boolean	1
tblDUR	OXY		Boolean	1
tblDUR	MTD		Boolean	1
tblDUR	BUP		Boolean	1
tblMedLog	MedID		Long	4
tblMedLog	Prescriber Last Name		Text	255
tblMedLog	Medication Prescribed		Text	255
tblMedLog	Dose		Text	255
tblMedLog	Patient		Text	255
tblMedLog	Date Prescribed		Date	8
tblMedLog	TypePrescription		Text	255
tblMedLog	Refills		Long	4
tblPAs	PAID		Long	4
tblPAs	Insurance		Text	255
tblPAs	Prescriber		Text	255
tblPAs	MRN		Long	4
tblPAs	Medication		Text	255
tblPAs	stPA		Date	8
tblPAs	expPA		Date	8
tblPatients	MRN	Will later be actual MRN, which are typically 8 digits, I think? Maybe 10.	Long	4
tblPatients	frtName	First name of patient	Text	255
tblPatients	lstName	Last name of patient, or for muppets, color	Text	255
tblPatients	vsched	How often they visit. I'd like a list of options but I don't know how (weekly, biweekly, monthly, three times per week, twice per week, probably still need room to add more potentially)	Text	255
tblPatients	PartIDFK	Their participant ID for study enrollment for the AUBU study	Long	4
tblPatients	phone	Phone number for contact (should I put in a secondary?)	Text	255
tblPatients	Address	Current address	Text	255
tblPatients	City	related to current address	Text	255
tblPatients	Zip	related to current address	Text	255
tblRanPC	PillCountID		Long	4
tblRanPC	dTePC		Date	8
tblRanPC	MRN		Long	4
tblRanPC	Medication		Text	255
tblRanPC	ResultPC	Appropriate or Not Appropriate are the possible answers	Text	255
tblReferrals	Referral ID		Long	4
tblReferrals	MRN		Long	4
tblReferrals	dTeReferral		Date	8
tblReferrals	byWho		Text	255
tblUDS	UDS ID		Long	4
tblUDS	dTeUDS		Date	8
tblUDS	MRN		Long	4
tblUDS	THC		Boolean	1
tblUDS	COC		Boolean	1
tblUDS	OPI		Boolean	1
tblUDS	METH		Boolean	1
tblUDS	AMP		Boolean	1
tblUDS	BZO		Boolean	1
tblUDS	MTD		Boolean	1
tblUDS	BUP		Boolean	1
tblUDS	OXY		Boolean	1

[ssanfu]

Looking at your dB, I would first suggest you rename the fields/forms that have spaces in their names.
Next, remove any special characters/punctuation in field names (as in "/"). Should only be letters and numbers (exception is the underscore).
"Type" is a reserved word and shouldn't be used as an object name. In this case, "ContactType" would be a better name.

The linking fields leave a lot to be desired. I've renamed fields (I use "_PK" and "_FK" as suffixes for PK and FK fields) and changed the links. I use Autonumbers as the PK field in 99.9% of my tables. If MRN can be 8 - 10 characters, then that field should be text, not numeric. And it certainly should NOT be the PK field.

In the description of a field in table tblPatients you have a question:

Field vsched
How often they visit. I'd like a list of options but I don't know how (weekly, biweekly, monthly, three times per week, twice per week, probably still need room to add more potentially)

You would use a table. In the form, you would have a combo box with the row source a query based on the Schedule table, since you are storing the actual text.

Look at the relationship window to see some of the changes.

I would look at the table "tblAUBUPart" again. I see where that table could be broken into maybe 3 tables.

Why is "vsched" in the Patient? Is "vsched" an attribute of a person?

I would also revisit the tables "tblUDS" and "tblDUR". The chemicals/drugs listed as field names are actually data.
What happens if you have to add 1 or 2 more drugs? You will have to modify the table, queries, forms, reports and possibly code. In a properly designed dB, you would just add the drug as an option in a table.

I created a couple of sub forms and added a tab control for demo purposes.


(sorry, I had a little time and maybe got a little carried away )

[jrichardson10]

ssanfu:

Wow! Sorry it has taken me so long to get back to the thread, the holiday really threw off our groove.

So I think your logic about the vsched is better than mine and that another table would work better, based on my limited understanding anyway. I put it in with patient just because I was trying to condense things, but that may have been hasty. When I open the patientrecord form, it prompts me to enter some data, which I don't quite understand. Some of our patients don't have an AUBU ID as they are not enrolled. Does it then make more sense to filter another way? Or is that actually a good way to separate... I suppose I would be interested in your logic there, just so I have a better understanding of how it work. Thanks again though, you rock!

[ssanfu]

When I open the patientrecord form, it prompts me to enter some data, which I don't quite understand. Some of our patients don't have an AUBU ID as they are not enrolled. Does it then make more sense to filter another way?

Oops....
I thought I had removed the two fields/controls that were not in the Patients table.

I still think that tables "tblUDS" and "tblDUR" need to be revisited - You have drug names as field names.

[jrichardson10]

No worries, I was just not sure what they were. I haven't touched those yet because I'm still working through the logic there. If I make a table of potential substances being used or found in the urine samples, then I can add as necessary without ruining forms. However, does that disrupt my ability to choose multiple substances for any given DUR or UDS? Theoretically, could I then have one table of substances and pull from one into two circumstances? This seems like it would streamline things. I'm going through the file you attached and trying to understand the changes and ultimately how I'd make such changes myself. Thanks again though, this is super helpful.