Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2008
    Posts
    5

    Unanswered: Time series measurement data?

    Hello guys!

    Am very new to databases and would require some help on designing the basic structure of one. My task is to implement a database for time series measurement data with Access. About 40 variables are being measured in 500+ timepoints.

    How would you implement the structure of such a database? Choosing the measured variables as attributes and use rows as timepoints would sound logical for storing one measurement, but of course one must be able to add data from new measurements to the database. And from what i understand adding a new table for new data is not the way to go...

    Ideally i would like to add a new row for a new measurement. But obviously i can't use timepoints as attributes, so what is the way to store data like this?
    Dividing it somehow? Probably the answer is simple and Access is up to the task, but as i said it's a whole new world for me

    thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    1 table, 41 columns. 1 column called "timepoint" (the primary key), the other 40 named after your 40 measures, 500+ rows.
    ????
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Poots, re-read the post mate:
    Quote Originally Posted by solonitsyn
    but of course one must be able to add data from new measurements to the database
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I read that in the light of the sentence before.
    Quote Originally Posted by solonitsyn
    Choosing the measured variables as attributes and use rows as timepoints would sound logical for storing one measurement
    OP - I think you sometimes use measurement to mean a single attribute and sometimes to mean values for all 40.

    How volatile will this be? How many new attributes are likely to come up?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2008
    Posts
    5
    Quote Originally Posted by pootle flump
    OP - I think you sometimes use measurement to mean a single attribute and sometimes to mean values for all 40.

    How volatile will this be? How many new attributes are likely to come up?
    Thanks for the answer and sorry for the confusion. One full measurement of 40 or so variables over time series is actually one patient as it's a medical application. The amount of attributes (measured variables) is likely to be fixed, or at least not changing much at all. The time resolution or measuring time can change more though.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If the attributes are fixed\ very slowly changing go for the design in post #2 and add columns as needed. Medical tests are notorious for their volatility though....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by solonitsyn
    The time resolution or measuring time can change more though.
    That's not a problem - each time point is simply a new row in the table. As such, it can theoretically accommodate an infinite number (though not in reality of course).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2008
    Posts
    5
    Quote Originally Posted by pootle flump
    If the attributes are fixed\ very slowly changing go for the design in post #2 and add columns as needed. Medical tests are notorious for their volatility though....
    Right. Now the table would contain one full measurement=one patient. Still wondering how to add a new patient though (i.e the data of a new measurement of all variables over time). The amount of patients tested is unknown but i think the database should be required to hold several hundreds if needed.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Unless you forsee a good reason to have one patient per table (and I can't for the life of me, unless this homework) then I'd suggest you need to do a bit of normalistaton. theres plenty of texts on the net about that...

    at a first glance I'd expect you to have a table for patients, a tabel for tests/obseravtions (with a link frm observations to patient (identfying waht patient had what test, and what the outcome is
    Id have a table which identified the type of test (eg hourly BP, pulse, LFT, whatever) and that to needs assocaiting to the tests table.

    you need to give some thought to what makes a test unique... suually these sort of questions are asked
    what makes it nique
    can a patient have more than one type of the same test
    what makes each test unique, or do I have to fabricate an atificial/surrogate key to make a record unique.

    as it happens I think you can and possibly should seriously consider the timepoint as a pk..... howevr you may elect to look at othr attribites, that either individualy or combined make a test result unique

    its arguable that you may have diferent types of tests/pobervations. you may have simple measurements sch as temp, bp, you may have lab tests, you may have entirely subjective assessments (eg autonomous reflex)
    some tests may require a numeric value, some may be a simple pass/fail,

    A blood test may comprise many individual tests.
    unless you know right nohow many tests are possible I wouldn't go down the route of one patient with n columns, equating to n tests. y'never jow when a new test may be invented
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sounds like EAVlite\ OTLT Mark. If the tests are not volatile then I would certainly go for 1 column per test measurement. You don't know if additional tests will come up, that is correct; nor do you know if your HR database will need to record blood type or eye colour in the future but that doesn't mean you go down the EAV route when setting up the person table. If he said the tests were volatile then I would agree, but he explicitly said they were not.

    Agreed re patients. I should have expanded after post #5 - I would add patient id into the table and make the pk a composite of (patient id, timepoint).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nah Im not advocating an EAV at all, I think they do have a role in our lives but not that often

    If what I think the OP's requirement is, is a mechanism to record details of tests/ observations on specific patients

    it depends on what the original requirement is
    if there are going to be a consistent battery of tests/recordings, ie there are truly 40 tests per timeslot then the single table with 40 columns, one for each test is reasonable.

    if however its a mixed battery of tests (al la carte so to speak) then the 40 column appraoch wont wash

    Ive seen a research db where there were a predetermined batch of tests (or being more cynical tick boxes) that requried checking. ie there were standardised procedures, however they also had other addon tests which were requested on an adhoc basis
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    if however its a mixed battery of tests (al la carte so to speak) then the 40 column appraoch wont wash
    Why not? Just leave inapplicable ones NULL....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2008
    Posts
    5
    Thanks for the help, guys. I'd say the point you both made about the volatility of the tests in a medical setting is certainly a good one. But as I said, for this study the amount of variables measured on the time series should remain more or less fixed. So I guess i'm able to work on that assumption. Of course one might want to store additional data (e.g weight, height) in the database, but that should be simple and I am now mostly concerned about storing the time series data.

    Quote Originally Posted by pootle flump
    I would add patient id into the table and make the pk a composite of (patient id, timepoint).
    Yes, i think this would work. So all the time series data of all the patients would be stored in a single table. I guess it's not a problem for a modern dbms to store it this way.

    So I think, I should have a table for patients, with the 'patient id' as an attribute matching the 'patient id' attribute in the table for the time series data. The table for patients could contain additional patient info as attributes.

    Sounds alright to you guys?

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by solonitsyn
    Sounds alright to you guys?
    Sounds spot on to me. Can't speak for "set a man on fire" though

    Quote Originally Posted by solonitsyn
    I guess it's not a problem for a modern dbms to store it this way.
    Better than that - it is how all RDBMSs (modern or otherwise) are intended & designed to store data.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Nov 2008
    Posts
    5
    Glad to hear!

    Ok, that will get me started then. Will probably be back for more dumb questions once i really get to it! Thanks again.

Posting Permissions

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