(newbie) Can't find how to set up my relationship for clinical database with followup

[nightale]

My question was really -- Do you ever have a PatientVisit and no questionnaires is completed?

Oh I see. No. Because this is a clinical research database and not a clinical database per se, I don't see patients if I don't administer at least one questionnaire! (unlike a doctor-patient following database where you record all visits)

Edit 2 : e.g. So if only 10 of the 16 questionnaires are administered for visit 2, the database must still create records for the 6 other questionnaires, but these records must be null (default value).

Oops posted too early! Ok thanks for taking that time, it is really appreciated!

 

[jdraw]

Edit 2 : e.g. So if only 10 of the 16 questionnaires are administered for visit 2, the database must still create records for the 6 other questionnaires, but these records must be null (default value).

That's the result but you do not store NULL values. You search for the values that exist. If you only give 10 tests of 16, then you did 10 tests --whose values are recorded in the database. If there is a maximum number of tests that could be given, you would know that and subtract actual (10) from maximum(16) and have your 6 no shows. But you wouldn't store NULL records in a database.

Some of the fields in the Clinic table would not be changing --Race, Handedness, FirstLanguage, but I understand things like weight, maritalStatus could change and belong on the per Visit Patient Info.


I have modified the model attached that I feel handles your requirements as I understand them. It would be good for you and others to review/critique the model.

In the old days we called this "stump the model". Many people representing different areas (factions) operations, design, development, management would pose questions/scenarios. The modeler and subject specialist would use the model to prove/disprove the validity of the model to support the scenario. The idea was to "attack" the model not the modeler! If a business rule was not supported, adjustments were made to the model until all requirements were satisfied.

Good luck. Hope it's useful to you.

After Posting Note:

Max, I wasn't suggesting you delete your post in other forum. Just identify this thread/forum so people aren't working in isolation.

 

[nightale]

Hi jdraw,

Thanks for your time. I just read the etiquette document (I think you added that as an edit, as I haven't seen it before re-reading the thread, sorry!). I didn't delete the post, but I added a reply and specified at the beggining it was a crosspost from this forum. I hope I haven't upset you. I wanted to seek other opinions to help us, since no one from my lab have experience with relational database...

The other guy from the other forum (Dal Jeanis) also told me I should make lookup tables for my questions. So since you both agree on that, I am now convinced (he told me if a revision of the test is made, I can only add one or two items and still be able to do comparisons, I didn't get that at first.

Let me take the time to understand your model and I'll reply back (just wanted to tell you I'm sorry if I upsetted you.)

 

[nightale]

Ok. Not storing null values makes a lot of sense now that you say it.

1) As I said in the other thread, I will make sure to put all patient data that will be stable (sex, dob, ...) across the study in the main patient table (tbl_Patient), all the rest (such as weight, waist size, bmi, etc.) in tbl_PatientClinicAdmin. I now understand this well.

2) Can you define exactly what you mean by test area? In my field, that would mean that :
- Geriatric Depression Scale and Beck Depression Inventory are two different instruments that belong to the same area = depression measures.
- CVLT-II and Wechsler Memory Scale both measure the same area = memory measures.

Is that what you meant?

3) Regarding your model

• Each TestArea can have 1 or more test = correct!
• One Test can only be related to one test Area = correct!
• Each test has one or more questions = correct!
• Each question is uniquely identified in a test = correct! (let's use the look-up table approach for that)
• The questions in a test won't change during the study = normally correct, but I am now convinced that I will make a lookup table to "futureproof" my database. Sorry about changing my mind.
• Each patient has info that spans across study and this is store in patient= correct!
• Some patient info may change across visits and this will be stored in PatientClinicAdmin = correct!

4) Now re: tbl_PatientTestALLEval
Each patient completes one or more tests/questionnaires at each visit = correct!

However, this is the only table that is confusing me a bit.

Given its attribute and its foreign keys to uniquely identify each rows of data, each PatientTestEvalID (PK) could correspond to :

A patient #001
In a test_area #depression
For atest_ID #geriatricdepressionscale
For a set of questions (question_ID) #15 items of the gds-15
At a given EvaluationDate
The responses of the patient to this set.

So that means, for one visit where patient #001 is administered all the 16 questionnaires, there would be 16 instances of PatientTestEvalID = 1,2,3,4,5....16 (ID could change depending on where the index is at)?

But If I want to extract all the questionnaires data for one specific patient at a specific time point, i.e. at T0, I have to find all PatientTestEvalID that corresponds to T0... that seems problematic, no?

Thanks!

 

[jdraw]

Hi jdraw,

Thanks for your time. I just read the etiquette document (I think you added that as an edit, as I haven't seen it before re-reading the thread, sorry!).

Response to your post #23
Max,
I recognized that you had not posted in these forums previously, and based on your questions, and the fact you are a graduate student working on your thesis that you had a real and interesting "business issue/opportunity". I enjoy helping others and to share some of the knowledge and experience I have gathered over many years in data management. As I was monitoring other forums and saw your original post (as I said not a true duplication), I felt I should mention the etiquette associated with cross-posting. And I'm sure it was an edit to one of my responses. Anyway no need to be sorry, and no, I'm not upset.

As you have seen, sometimes posts get edited. Sometimes posts get held for review before they are posted. Sometimes a number of responses go back and forth before the earlier edited info is read. When I went back to the other forum and saw no link, I added it. Not upset, but trying to give anyone reviewing your post the additional info that you and I had evolved in this thread.

I didn't delete the post, but I added a reply and specified at the beggining it was a crosspost from this forum. I hope I haven't upset you. I wanted to seek other opinions to help us, since no one from my lab have experience with relational database...
No reason to be sorry. We're here to help - especially those who are reading and learning and putting in an honest effort.

The other guy from the other forum (Dal Jeanis) also told me I should make lookup tables for my questions. So since you both agree on that, I am now convinced (he told me if a revision of the test is made, I can only add one or two items and still be able to do comparisons, I didn't get that at first.

Yes, Dal is experienced and thorough. In fact, he, June7 and AlanSidman are some of the people who could critique the model I suggested,and/or offer their own design ideas.

There are many people on this forum as well who could offer design assistance.

 

[jdraw]

Max -- Regarding your post #24

1) Yes, as we had discussed, some attributes could change during the study. So it makes sense to store these Patient data facts with each Patient/Visit/Evaluation. And as you are saying, a review of the attributes to get the appropriate static and variable facts into the proper table is good.

2) As for TestArea, I was trying to separate you Depression, Dementia and Apathy into some higher level categories. I wasn't sure, and had a guess, that some of these areas could have a number of recognized tests to measure/evaluate similar "characteristics/issues". As you can see this is not my area of expertise - but as analogy
you could test Depression by means of response to questions; interpretation of drawings...So in the DepressionTestArea you could have several "recognized" tests.
Bottom line -yes, that's what I meant.

3) The number of corrects is good --indicates we are now talking and communicating. Futureproofing is good, and now allows the model to be adjusted and refined (as more detail is found). This is the ways things should go and believe me - "users" always change their mind (or said differently - many had their minds made up until someone - outside of their field of expertise - asks a question or provides a thought, that turns on a light).

4)A patient #001
In a test_area #depression
For atest_ID #geriatricdepressionscale
For a set of questions (question_ID) #15 items of the gds-15
At a given EvaluationDate
The responses of the patient to this set.

So that means, for one visit where patient #001 is administered all the 16 questionnaires, there would be 16 instances of PatientTestEvalID = 1,2,3,4,5....16 (ID could change depending on where the index is at)?

But If I want to extract all the questionnaires data for one specific patient at a specific time point, i.e. at T0, I have to find all PatientTestEvalID that corresponds to T0... that seems problematic, no?

Well, you know the PatientID, and you know the EvaluationDate, you know the TestID, so you could find all questions and responses for that Patient for that Test on that Date.
As the model is at the moment, there would be a unique PatientTestEvalID for every
Patient, TestArea, TestId, QuestionID and EvaluationDate. This could be adjusted, if and as needed, and tested.

eg:

Select all info from PatientTestAllEval
WHERE
PatientID = 001 and
EvaluationDate = #23/07/13#
would return all info TestAreas, TestIds, QuestionIds and Responses for Patient 001
on 23/07/13.
This can be sorted and grouped as needed.

Good questions -- as we get more critique/suggestions, things will become more clear.

 

[nightale]

Thanks!

I posted a reply on the other thread if you want to take a look. With your help and Dal's, I think I'm very close to the design I was looking for.

Your test area is really a good idea, because it falls in line with my research hypothesis. For example, I'll check how cardiovascular risk factors are correlated with deficits in executive functions (one test area), attention functions (another test area), episodic memory (another test area), etc.

With regards to Visit ID... The reason I stick to that is because I need it as a common field for everyone (no matter the date, the first evaluation for everyone will be T0, the second will be T1, etc.). That way I feel it'll be MUCH easier to query my data by choosing "T1" in Visit field, instead of selecting the date for every patient (or to query the earliest date recorded). What do you think? (I'm not sure I'm clear, sometimes my english eludes me).

So basically, regarding your schema "Max_NightalePatientTests_V2.jpg", I just have to add a field in tbl_PatientTestALLEval where I specify whether it is T0, T1, T2, etc... That field would be a lookup to tbl_Visit where I'll simply enter 1 = T0, 2 = T1 ; 3 = T2; 4=T3 ; etc.

I'm glad you are not discouraged by my obvious lack of knowledge in this domain. Thank you so much.

 

[jdraw]

Visit or Evaluation seem the same to me. That's when a Patient completes 1 or more questionnaires. Suppose Patient 1 does his first set of 16 Tests/Evaluations on July 12,
you could have a table that relates

Patient, EvaluationDate and relate that to your T0
If that same Patient does his/her second set of tests/Evaluations on Oct 27, then that could be T1 for that Patient.
You will need to relate the corresponding Id (PK) for your T values to the TestArea, Test, etc. sufficient to make the data retrievable for your purposes.

If a Patient doesn't complete all 16 Tests on the same day, then your T value could refer to more than 1 Date. Suppose Patient 6 finishes 11 Tests on July 18-- and for some reason can not complete the other 5 Tests, but returns on Aug 2 to complete those 5 Tests. This indicates a given T value may relate to multiple dates.

If you drop the whole EvaluationDate approach and just go with T0, T1 etc., then you could just identify the first, second, third visit. I see no issue with that if it will handle the situation above where not all of one set of Tests are done at the same Visit.

I've looked at the other forum and Dals comments, all seems good. New ideas, more details, things to look out for etc--all good stuff. He seems to understand and speak the same "lingo" clinic-wise.

In your eg. of Vertical design with your PatientId + VisitId etc. , your QuestionnaireID is equivalent to my TestId. So the thinking and examples are certainly along a common theme.

Again, good luck.