Clinical database - relationship for patient that can have multiple follow-ups

[nightale]

Hi guys,



I'm new to Access although I've read countless tutorials and guide on how to design databases and on how to use Access.

Nonetheless, I'm still uncertain with my approach. Please see my relationship screenshot and tell me if I'm off to a good start.

Basically, I'm doing a clinical database where all patients are listed in "tbl_Patient". The PK is "Patient_ID".

One patient can have many visit. So I created a tbl_PatientVisit with a one-to-many relationship. In this table PatientVisit_ID is the autonumber PK and the table represent the combination of a patient with a visit and a timestamp. The tbl_Visit is only a lookup table where I've put text tags for the Visit_ID that is a PK number field (1= baseline; 2= first follow-up; 3=second follow; 4= final follow-up).

This is where I'm confused...

At each visit, I do a complete re-assessment of the patient with different questionnaires. The first one is the tbl_Patient_GDS-15 which is a depression questionnaire with 15 items (each one is a field). The second one is an apathy questionnaire and is named tbl_Patient_ApathyScale; it has 14 questions and each one is a field. The third questionnaire is tbl_Patient_DRS2 and is a dementia questionnaire and will have over 200 fields when I'll finish designing the table.

My rules:




So basically, one patient can have many visit. This creates a PatientVisit_ID.

One patient visit (PatientVisit_ID) can only have one record by questionnaire; one for each questionnaire type (tbl_Patient_GDS-15; tbl_Patient_ApathyScale; tbl_Patient_DRS2). Because in real life one Patient_GDS-15_ID = one questionnaire printed on paper, one Patient_ApathyScale_ID = one questionnaire printed on paper and one Patient_DRS-2_ID = one questionnaire printed on paper, each of them can be related to only one PatientVisit_ID = one patient visit, this creates a one-to-one relationship.

My first question : Up to now, Am I seeing this the correct way? I know I could merge all my questionnaires table into one, but some of them have will over 200 fields (and I'll add a total of 16 questionnaires), so I wanted to divide them.


What I'm aiming at :
To create a user interface for my assistant to type in data.

There'll be a "tier 1" form where the user select/creates a patient_ID file (add new patient for instance). Once selected or created, they'll be at a "tier 2" form to select the visit_ID and the time of this visit. They'll have buttons to select or create a new visit for the pre-selected patient at step 1. Once visit selected, they will open the "tier 3" form where all the tests are organized within tabs and they can record the patient responses for this visit for all questionnaires...

Please see the second screenshot to have an idea of what my data would look like in a redundant Excel spreadsheet. Of course, I only included two mockup questionnaires with 4 questions each


My second question : So far, Am I ok with my form design?

On my end:

I'll create custom queries to extract data and analyse it in SPSS/SAS/MathLab.

I want my data to be organized so that I can apply filters (criteria) to my needs.

For instance : extract all the data from all patients over 70 years old, with a score >4 at baseline (T0) on the GDS-15 questionnaire.
Other example : extract all T0 (baseline) questionnaires (all questionnaires) data from all patients over 50 years old.


Report example : show me all the patient that have had at least one follow-up.

My third : Will I be able to do that with such an approach? Should I reconsider some aspects of my design?



I thank you so very much, it is really appreciated.

Regards,
Max

[Dal Jeanis]

First impressions -

1) The ClinDemo table is obviously problematic. (Probably not a correctly defined entity). You have it linked to each visit. Does your client's race and first language change over time? Perhaps the information all happened to be on your intake form? You might consider having an initial intake form that gets permanent information, and a separate iterative query for changing information.

Move permanent information to the Patient table. Stable information that is relatively simple (marital status) could also be on the patient record, or could be on a separate file that is linked only to the Patient record. I would not expect to see the degree information changing much. Also, degrees, professions and jobs (to patient) should be many to one, so they probably belong on a separate table. For your purposes, I would use a single table for all those professional life events, since they are relatively stable and noncomplex.

Likewise, Domicile should be on a separate table.

2) Standard design for questionaire tables is to have
(A) One table that holds survey questions.
(B) One table that identifies Groups of questions (ie instruments or Surveys).
(C) One junction table that links each survey to the particular questions on that survey.
(D) One table with information about the particular instance of one person taking one survey. (Date, score, notes)
(E) One junction table with the particular answers given by that person regarding that survey question.

This strategy is not perfect, but it has the advantage of being all the tables you would ever need to do a thousand similar surveys. And if one of your instruments ever changes, for instance if Stanford updates the GDS-15 due to the many changes in American English since 1986, then you can create a new version of the instrument in table B, using the same items from table A that were retained, and adding into table A any new or revised items that Stanford created. If you ever do statistical comparisons, you can maintain validity across versions for the unchanged items, and selectively compare the revised questions as appropriate.

THE BIG PICTURE

The main item I want to bring to your attention is that this is a "tall thin" table design, not a "short fat" one. Your 200-item survey is 200 rows in a single table, not a table with 200 columns. The simplicity that this brings to your queries is amazing.

LATER REFINEMENTS

You could add a scoring table to the mix as well. In the case of the GDS-15, you'd have only one entry per item, entering only the single entry, yes or no, that counts as a depression point. (you don't have to enter answers that result in zero points, although you could do that for clarity.)

With the MBTI or certain other items on a Likert scale, you might have one scoring entry per each possible answer, or even one entry per possible answer per subscale, if a question might affect multiple subscales. But that's down the road. For now, just look at it as a single simple entry method, once.

[Dal Jeanis]

FYI, here's what the survey tables might look like:

tblQuestions
   QuestID     PK, AutoKey
   Question    Text

tblSurveys
   SurvID      PK, AutoKey
   SurvName    Text
   SurvNotes   Memo

tblSurvQuestions
   SurvQuestID PK, Autokey
   SurvID      FK to tblSurveys
   QuestID     FK to tblQuestions
   ItemNumber  Number  (question order within the survey)

tblPatientSurveys
   PatSurvID   PK, Autokey
   PatientID   FK to tblPatients
   SurveyID    FK to tblSurveys
   PatSurvDate Date
   
tblAnswers
   AnswerID    PK, Autokey
   PatSurvID   FK to tblPatientSurveys
   Answer      text

Yep, that's it. See why tall skinny is easier? Table design is done, and it's just data entry of the actual items. By the way, you don't have to type the actual predicates in... there's nothing wrong with letting question #1 on survey GDS-15 be "#1". Although, if you ever want to move to your patients answering the instruments on the computer, there's no reason you couldn't do that.

And here's the two tables I'd add if I was letting Access either do my instrument scoring or using an automated scoring techniques to verify accuracy of my data entry:

tblSubscale
   SubscaleID  PK, Autokey
   SurvID      FK to tblSurveys
   SubscName   Text
   SubscNotes  memo

tblScoring
   ScorePK     PK, Autokey
   SurvQuestID FK to tblSurvQuestions
   SubScaleID  FK to tblSubscale
   Answer      text
   Weighting   number

[orange]

Related post:
http://www.access-programmers.co.uk/....php?p=1311612

[nightale]

Hi,

Thanks for your help. I'm impressed by your dedication to help me, it is really appreciated. First of all, I'd like to mention that I "cross-posted" this question before on another board and that's how I achieved what I have now (thanks jdraw). I came here to have other comments about this, to seek the opinion of other experts as well. The other board is : http://www.access-programmers.co.uk/...12#post1311612 .

Ok back to business...

RE: Point 1 :
You are definitely right and this is an artifact of changing my table structure a lot of time. Every piece of patient info (e.g. sex, race, first language) that will be stable across the study span will stay in the tbl_Patient. Every piece of patient info that is likely to change (e.g. domicile, weight, height, bmi) will be stored on a separate table similar to those for questionnaires.

RE: Point 2:

Part 1

(A) One table that holds survey questions.
(B) One table that identifies Groups of questions (ie instruments or Surveys).
(C) One junction table that links each survey to the particular questions on that survey.
(D) One table with information about the particular instance of one person taking one survey. (Date, score, notes)
(E) One junction table with the particular answers given by that person regarding that survey question.

Can you clarify some things for me?

Point A) Will that table hold all the questions across all questionnaires?
Point B) And this table holds all the instrument name I have?
Point C) This links all instrument to their respective questions, right?

tblQuestions
   QuestID     PK, AutoKey
   Question    Text

So basically, each and every questions of all my questionnaires will represent a row in this table and will be associated with a QuestionID ? And each instrument (i.e. test or questionnaire -> you named them surveys) will be listed in a table with its name and an unique Instrument_ID?

Like in this screenshot (mock sample of questions)? But what does the ItemNumber field do?


Part 2
For tables 4 and 5, you suggested...

tblPatientSurveys
   PatSurvID   PK, Autokey
   PatientID   FK to tblPatients
   SurveyID    FK to tblSurveys
   PatSurvDate Date
   
tblAnswers
   AnswerID    PK, Autokey
   PatSurvID   FK to tblPatientSurveys
   Answer      text

So tbl_PatientInstruments (tblPatientSurveys) is associating a patient id with an instrument id and a timestamp. Sounds good to me. However, to respond to my need of having more than one visit (baseline, first and second follow-ups and last follow-up), should I add the Visit as a FK to PatientSurvey? Patient #x can only have one record of questionnaire (PatientInstrument_X) per visit and one visit_ID can only belong to one patient.

Example I've found somewhere else :





But I don't understand how table 5 (tblAnswers) works?

[nightale]

Thanks orange, I've specified it in my reply (I think it is waiting for approval, but I can't find any trace of it... I hope it is because it's a long reply :P)

[pbaldy]

Just approved it.

[Dal Jeanis]

A, B, C) You have it basically right.

Item_Number) The order of the question in the instrument. This is primarily an ordering mechanism. Relational databases do not have any inherent "order" to the rows that are stored in the table. If you set up a query, the results may be displayed in any arbitrary order, unless you use the GROUP BY and/or ORDER BY clauses. They might, for example, be sorted by the alpha order of the wording of the question.

Suppose, for example, two different versions of an instrument have the same questions in different orders. Or, suppose that the same predicate is used in multiple instruments. Item_Number is the way you tell the system what order to return the results in.

Another random note - Since the GDS-15 and GDS-30 share overlapping questions, if you were to add GDS-30 as a potential instrument to be used for some patients under some clinical conditions, you wouldn't usually enter the duplicate questions twice, you'd just include the other 15+ questions into tblQuestions, and control the order in tblInstrumentQuestions.

Part 2

Yes, you could add the foreign key of tblVisits to tblPatientInstruments. In that case, VisitID would replace the PatSurvDate, since the date could come from the visit, rather than being stored on tblPatientInstruments. If your patients might occasionally fill out an instrument remotely without visiting, or might bring in an external copy (an instrument taken at a prior clinical office or at an inpatient facility) then you need to decide whether to maintain "pseudo-visits" in your database or keep the tblPatientInstruments by date instead.

You can always use a query to match up the instrument date to the nearest visit date later if you want, it doesn't have to be directly linked to a visit unless that information is critical to understanding the instrument results. Query-wise, I don't see how it could be mandatory. Your call, though.

[Dal Jeanis]

Sorry , missed a field on the Answers table. No wonder you were confused.

tblAnswers
   AnswerID    PK, Autokey
   PatSurvID   FK to tblPatientSurveys
 QuestID     FK to tblQuestions
(Or InstrQuestID  FK to tblInstrumentQuestions)
 Answer      text
   AnswNotes   text

Some sample results. I've shortened the key field names to shorten the posted layout:

tblQuestions
 QKey    Question
   1     Are you basically satisfied with your life?
   2     Have you dropped many of your activities and interests?
   3     Do you feel that your life is empty?
  ...    ...............................................
  14     Do you feel that your situation is hopeless?
  15     Do you think that most people are better off than you are?
  ...    ...............................................
  101    Do you ever feel Angry?
  102    Have you ever killed in Anger?
  103    Do you feel remorse over injuries caused by your actions?

tblInstruments
 IKey    Instrument
    1    Geriatric Depression Scale - 15
   ..    ..................................
   12    Avengers Stability Assessment V1.3

tblInstrumentQuestions
 IQKey  QKey  IKey  Order
    1     1     1     01
    2     2     1     02
    3     3     1     03
   ..    ..     .     ..
   14    14     1     14
   15    15     1     15
   ...   ...   ..     ..
   129   101   12     01
   130   102   12     02
   131   103   12     03

tblPatients
 PKey   Patient  
  87    Bruce Banner
  ..    .............
  98    James Logan Howlett

tblVisits
 VKey  PKey    VDate
  421   87   11/20/2013
  ...   ..   ..........
  457   98   11/27/2013

tblPatientInstruments
 PIKey PKey IKey    PIDate     (or VKey)
  234   87    1   11/20/2013    ( 421 )
  235   87   12   11/20/2013    ( 421 )
  ....  ..   ..   ...........     ...
  243   98   12   11/27/2013    ( 457 )

tblAnswers
  AKey PIKey QKey  (or IQKey) Answer   AnswNotes
  3891  234    1     (  1  )    Y  
  3892  234    2     (  2  )    Y  
  3893  234    3     (  3  )    N  
  ....  ...   ...    .......    .
  3904  234   14     ( 14  )    N  
  3905  234   15     ( 15  )    N  
  3906  235   101    ( 129 )    Y       I'm always Angry  
  3907  235   102    ( 130 )    N       It's the Big Guy that does that
  3908  235   103    ( 131 )    Y       But I'm sorry about it anyway 
  ....  ...   ...    .......    .
  4427  243   101    ( 129 )    Y       Hell, Yeah  
  4428  243   102    ( 130 )    Y       Not nearly enough as I should
  4429  243   103    ( 131 )    N       Hell, No

The choice of whether to link tblPatientInstruments by visit or by date is somewhat arbitrary. There are lots of valid normalized ways to link up the tables.

For instance, you could link tblAnswers to QKey or IQKey to achieve the same result. I suspect IQKey might be a better choice, so use that option in the schema above unless you can think of a good reason to use Qkey.

Also, it would be valid to have both visit and date in the table, if you planned to allow visit to be Nullable, or to allow patients to fill out instruments in advance or to take them home after the office visit, or if you ask an occasional patient to fill an instrument out at home as a followup to treatment a few months later, and so on. Business rules, and potential business rules, should inform your design.

[Dal Jeanis]

DATA ENTRY

I noticed on the other thread http://www.access-programmers.co.uk/...d.php?t=257379 that you were planning on having your data entry be entered and checked by two persons.

You might consider having a dual entry system, where each item is entered twice freshly into a temporary table structure, and then the results can be compared directly and any differences automatically flagged for resolution. With this strategy, a single person doing the dual entry (preferably not entering the same patient and instrument twice in a row) can be just as valid operationally as two different persons doing the entry.

My wife used something similar to that for her Ph.D. thesis renorming an instrument for elderly patients - the Brief Symptom Inventory (BSI) by NCS/Derogatis. This method is much more accurate than having a person review the pre-entered data against the paper instrument.

This method would require a temporary Answers table, laid out just like the regular answers table. You'd also have a InstrumentEntry table that is very similar to tblPatientInstruments, with an additional field for first/second/third...etc entry. When the same instrument had been fully entered two or more times for the same patient and date, the "validate" button would allow error checking. If differences were found, only the variant responses would be presented for reentry. If no differences were found, then a button would activate to allow the data to be copied into the real Answers table, and archived from the temporary table.

SUBJECT AREAS

Also, on reviewing the other thread, I noticed that you are planning to link your tests on subject areas. This is just a suggestion, since I haven't reviewed all your 16 instruments, but you might consider integrating my Subscale suggestion from post #3 in this thread with your thoughts on subject areas. An entire instrument like the GDS might be related to depression, but for the BSI that might be just one of its subscales. As such, you might have a tblSubject that lists the possible subjects, a junction table tblSubscale (or tblInstrumentSubject) that links tblInstrument to tblSubject, and a junction table tblSubscaleQuestion (or tblSubscaleAnswer) that links each question (or answer) to one or more tblSubScales, and possibly provides weighting/scoring info.

That's a complex structure, but it has the advantage that you can defer decision and add it later (or build it now and populate it later) if you decide there's value in it for your analysis phase.

[nightale]

Oh! Thank you so much, it makes a lot more sense now. Combined with the help of jdraw from access-programmers I have a better idea of my design. He also strongly suggested to always use an approach where I have some flexibility with regards to questions.


Re: the field Item number
Oh! I see! This field specify the item number within the instrument. Question_ID couldn't do that, because once you switch instruments, the numbering still continues with n+1 (because all the questions of all my tests are in one single Question table). Your comment about the GDS-30 is very relevant, because my first 10 patients were administered the GDS-30, but I had to make changes to shorten the battery and now I use the GDS-15. If for some reasons I ever switch back to GDS-30 for this study or I use it for another study, I'll use that approach.



Re : vertical design
I've made some research and it confirmed me that you are right, this is a good approach for my needs. Regarding the "coding" aspect, I'll get in the end something like that, which is exactly what I need to extract my data with queries :



JDraw suggested my test areas, which is a good idea because it is in line with my hypothesis. Now that you talk about subscale... it is mandatory that I think in terms of subscales. Tests like the Mattis Dementia Rating Scale have 5 subscales (attention, initiation/perserveration, conceptualisation, construction and memory) and each subscale is validated as one.

I'll implement that "subscale" approach into my design and I'll use the structure you suggested. However, most instruments will have only 1 subscale.


Re : patient visit
I will use the patient visit table, because I will need that field for queries (see my "coding" approach.). I think it'll be easier than to specify dates or something like "choose earliest date". For your point about questionnaires that aren't done during one visit, I think this won't be necessary for me, because by visit I really mean "timepoint 1 = baseline", "timepoint 2 = first follow-up", etc. A record will be necessarily attached to a visit.


Re: data entry and validation
This seems a very good idea. (thinking to myself OMG! this can get so overwhelming, but I feel like I'm learning a lot). I want to do that. First, I'll take the time to set my database straight and then I'll integrate that part into the design. Thanks for this idea. It'll definitely impress my thesis committee when I'll explain them how I made sure each record was validated with that system. That way, one person can do all the data entry once. Then, either the same person start over or a second one do the data entry for the same sets of paper forms and confirm with a validation field. If a problem is flagged, the assistant can take the time to select the correct response.

Thank you so much and sorry for omitting to include the other thread at first. I'm new to this forum world and I'm very impressed how some people are dedicated to help someone else.

Regards,
Max

[nightale]

Dal, I almost forgot.

With that approach, I am better off typing every questions in Excel and then importing into Access, right? That way in Excel, I first have relevant data in adjacent columns. Then, I can import each column into it's relevant table in Access. I think that will be easier and less time consuming.

What do you think?

[Dal Jeanis]

I find that, for initial mass loading of simple data, Excel is often a good tool. Clearly superior to a badly designed Access form.

OVERWHELM

When you look at this process, remember two questions - how to eat an elephant, and how to teach a chicken to play the piano. (Answers - One bite at a time, and successive approximation).

Access is an excellent tool for iterative design. You do NOT have to get the design perfect, and you do not have to get all the data into the database at once.

In fact, you can start off your question table by creating a dummy-insert process. When you enter a new instrument, have a form that asks for the instrument name, instrument abbrevation, and the number of items. Then click a button, and it creates that many dummy questions where the values of the question text are "GDS-15 Item 01", "GDS-15 Item 02", "GDS-15 Item 03" and so on. You can replace that text later with the actual item text, when you have the time. Aside - remember your database is just temporarily representing the instruments and allowing analysis of their results, the database ISN'T the instruments and isn't a substitute for them. The instruments are copyrighted and so on.

VISITS

Okay, then you should give a smidgeon of consideration to how to specify a "virtual" patient visit in your database. It doesn't matter what method you use for it, precisely, but even eighty real life persons are going to kick up some funky situations in the time of a typical study. Hmmm. Just the idea that for one client there may be an "intermediate" followup in between the first and second scheduled followups... How would you handle that? Suppose there were a life event that precipitated a clinical event where instruments were filled out. How would you handle that?

Also, you might want to allow for a potential later publication if you get the chance to follow up your study after six months, a year, etc. Don't make yourself crazy, but allow for expansion and adaptation as needed. The key is simple design, and flexible design to support the abstract potential situations, rather than setting your current expectations in stone. (Like, we expect four visits per patient, so we have one patient record with four slots that each holds a visit. !)

[nightale]

Hey Dal,

Haha I would like to see that chicken on the piano :-)

Regarding the dummy questions, that's exactly what I'll do first to have a "test" dataset. Before entering any real data, I want to get a hold on how to design the next part, i.e. forms. I'll have to read more on that before.

Visits : I was thinking of something to overcome that problem, even though it is not likely it will happen, at least during my thesis study, because I don't have the ethic committee approval and I don't have the time during my Ph.D training to do more than 1 follow-up for only a portion of my patients. My "solution" : instead of labeling my visits like T0,T1,T2, etc., I will assign them a timepoint : 010, 020, 030, 040. This leaves me 011 to 019 between each timepoint to add one if I need to. Is that a good idea?

What do you mean by "you might want to allow for a potential later publication if you get the chance to follow up your study after six months, a year, etc."? (sorry language barrier I think).

[Dal Jeanis]

The piano playing chicken is/was a sideshow gag in the circus. A big glass box with a chicken, a little flag and a toy piano. You put a quarter in, the flag goes up to tell the chicken it's time to play. The chicken pecks the toy piano a few times, then the flag goes down and the chicken receives a bite of corn for its trouble. You teach the chicken by rewarding behaviors that get successively closer to the desired result - first you reward her whenever she pecks, then when she pecks one wall, then reward successively smaller circles until she pecks a bullseye, then put the bullseye on the piano.

Visits - That can work. It's still pretty specific. I wonder if there's a higher level of abstraction that does away with the number sequence completely?

Publish or perish. Once you've finished your dissertation, if you go the academic route, you'll need to pursue further publications. That means that considering potential followup studies in advance of starting data collection is a good career move. Don't let it slow your dissertation down, just give some thought to "what are the likely next steps to build on this study?". Is there a potential longitudinal study if you follow up in three years? The reason to consider it now is that your docs for your ethics committee (and the consent forms) will include your specification of when (or if) you will destroy the personally identifiable data. If you want to do a longitudinal study, or some other expansion of the dissertation, then you need to give it some thought up front.

Like I said, don't slow your doctorate down with blue sky, but see if there's a clear path to reuse/repeat/expand your study. By the way, if the BSI is one of your instruments, and you're studying an elderly population, then you need to talk to my wife. Message me and I'll give you her phone number.