Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: Database for retrospective analysis of medical data (noob alert!)

    Hi Guys!

    I've been tasked with mocking up a system with which we can analyse some medical data - Basically what we have is a small patient cohort (~500), in which each patient has been screened for a group of medical conditions. The tests for some of the conditions can contain multiple different results - rather than just a single numerical value (others are just individual numerical values). What we want is to have this data in a database, and then be able to query for patients above test thresholds for a particular condition etc...


    I have very little experience with Access - I'm only just beginning to look into how Im going to go about this, but was looking for some thoughts on the general direction Im thinking in ---- i.e. whether Im completely off target or not





    Based on my couple of hours of Access research this is the general approach I was thinking;


    table1 - patient details:

    patientID (primary key), firstname, lastname, DOB, sex, countryofbirth, etc...

    table2 - condition1:

    condition1reportID (primary key), patientID, date, testvalue1, testvalue2, etc....

    table3 - condition2:

    condition2reportID (primary key), patientID, date, testvalue


    etc etc...



    What I'm unsure about is this - Most of the tests, e.g. condition2 above, are only performed once. However, some require consecutive test results for a positive diagnosis - lets say for example, a certain numerical value on two testing dates spread a year apart.... what is the best way to deal with follow up tests like this?

    I was thinking of some options

    a) using two tables - lets say condition1first and condition1second.

    Then we could, say, query the patient table, condition1first and condition1second, and look for patients with both testvalue1 > some threshold value


    b) using a field within a single table to designate which round of testing the record is - e.g. a field in which we enter 1 for the first test, 2 for second etc...

    but i dont really understand how this case would be queried for consecutive results above a threshold, versus one above one below etc...



    To me a) makes the most sense.... the only other thought I had was to keep only a patient details table, and enter both personal details and test results, but that seems like it would get really messy....




    I realise it's early in the process to be hitting the forums for help, but would appreciate any thoughts!


    Cheers!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    One table for patient information.
    One table for condition information.
    One table for the results of each patients' tests against each condition (holds the patient ID, condition ID, test result, date, etc).
    This will allow you to run reports based on patient or condition.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id be very very wary of having one table per condition... the reason
    it can become a pig to maintain over time. if someone decides to add new conditions you have a problem (you have to change the method of storage and the user interface). granted this may not be applicable to you given that this is a historical analysis.

    If it were me, I'd have...

    a table for countries, probably using the ISO 3166 3 digit code

    a table for patient details, bear in mind Access is tricky to secure so there may be data protection issues in secure personal data. you may be beter off using another data stroage mechanism if privacy is critical and cannot be guaranteed by where the application is run. if its on a network on a publiclly visible device you coudl have security / privacy issues, if its on a restricted network location and not visible to the outside world then you may be OK with Access.

    a table for conditions

    a table for test types (eg blood, urine, skin biopsy whatever)

    a table for tests. this should define what type of test it is (eg looking for a specific enzyme or genetic marker say within a blood sample) and should also define what are min/max threshold values, or pass fail whatever also define minimum / maximum thresholds. so the db 'knows' what is an acceptable result. arguably units should be a separate ,table referring to the test

    a table that defines what suite of tests you'd do for a specific condition. effectivley this is an intersection table of tests and conditions, recognising that one patient may have more than one condition, and one condition may be present in more than one patient

    a table that contains details of a specific sample / lab session (eg the date, the patient and so on)

    a table that records the observed / measured value of a specific test as defined in the testsuite referred to above (ie the conjunction of Conditions AND Tests). resist the temptation to record if a test passes or fails on a threshold value as you can redefien the threshold as part of the test definition. say the research demonstrates that a mmol or 50 is the new min threshold then storign the pass measn you hae to requery the databasae and update a pass / fail flag. whereas changing the test threshold will automatically work out whether its a pass or fail. you can also do soem form of sensitivity analysis (ie number of samples within n% of the threshold

    but then I'm no medic or medic researcher so what do I know
    Last edited by healdem; 06-29-11 at 10:12.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2011
    Posts
    4
    Wow Thanks guys - I appreciate your thoughts!


    @healdem: Haha I'm no medic myself, but here I am anyway... lol

    I see what you mean re: security - I had read that before. This is really only a mock up - any actual use would end up using either deidentified data and be on an isolated machine, or be tasked to someone more experienced

    Your approach definitely looks much better - to me it certainly seems worth the effort to try future proof things as much as possible, rather than back myself into a corner if someone decides to change things! haha

    Just to clarify some of the points you made...

    a table that records the observed / measured value of a specific test as defined in the testsuite referred to above (ie the conjunction of Conditions AND Tests). resist the temptation to record if a test passes or fails on a threshold value as you can redefien the threshold as part of the test definition. say the research demonstrates that a mmol or 50 is the new min threshold then storign the pass measn you hae to requery the databasae and update a pass / fail flag. whereas changing the test threshold will automatically work out whether its a pass or fail. you can also do soem form of sensitivity analysis (ie number of samples within n% of the threshold
    So this would be one table containing the results/measurements for each test? or one large table with a field for measurements and test type for each record?



    The thing I think Im most worried about is how to handle follow ups where the same test is performed. Is this as simple as using a date field in the test measurement tables?

    On that note, would the following be possible?

    Say we were looking for all the patients confirmed by their test results to have a certain condition. The condition in question requires a positive test on 2 consecutive tests.

    Is it easy to query for all patients with consecutive positive tests?





    Thanks again guys! I really appreciate the help!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one table for tests, not one table per test type, reason is the same maintenance over time, increased development time and so on.

    one potential issue is if there are retests done on the same sample or whether effectively a retest is a test on another sample. if so you modle has to support that. actually its not a biggie as if you need to support the retest methodology you could, say, store the date the sample was tested which may or may not be the same as the date the sample was taken.

    writing the SQL to pickup consecutive tests and make comparisons is tricky. it can be done but its over and above my pay grade. there's others here far more capable at such SQL mastery, one of which is Rudy.. I'll drop him a pm and see if he can help
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the alert, healdem, i've had a look at this thread and all i can offer is that yes, it is fairly straightforward analysing consecutive rows

    when we get to the point here where we've actually got some tables defined, i'd be happy to work up some SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2011
    Posts
    4
    Thanks again healdem! I'm completely out of my depth, so any help is welcome

    I was thinking, since we're talking about historical data, it will be known at the time of input into the database if the patient in question has had a particular test performed multiple times. If that's the case, I was thinking it might help to have a field in the test results table where we manual designate if this is the first, second, third etc...

    In my mind, Im thinking that that should help - maybe having one query to look for all the test results of a certain type with a "2" value, that is, all of the second tests and another for all the first tests, and then applying some form of selection criteria where we select out only those patients whose first value AND second value was above the threshold....??

    Maybe that would be possible using the dates and some form of counter for each patient though?

    Now off to look for some Access and SQL resources to see if I'm completely dreaming that I can do this

  8. #8
    Join Date
    Jun 2011
    Posts
    4
    Quote Originally Posted by r937 View Post
    thanks for the alert, healdem, i've had a look at this thread and all i can offer is that yes, it is fairly straightforward analysing consecutive rows

    when we get to the point here where we've actually got some tables defined, i'd be happy to work up some SQL
    Thanks for looking in r937!

    I think you must have posted right when I was writing that last message... I was planning to start setting things up in access over the weekend - I'll see where I get, and post my table set up on here then!




    I must say, I've never been on a forum where people are so receptive and willing to help!

    Much appreciated guys!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •