wood & trees

[jpadie]

I'm helping out a community in the US designing a database webapp to hold information on medlab tests and similar.

and ... I'm running into some design issues that have meant I haven't progressed for a while.

Some background

1. i design apps so that there is a mapping between classes and entities.
2. entities typically (but not always) map to database tables
3. entities typically map to web-forms.
4. statistical analysis on the data is vital to the usefulness of the app. both per lab result and across all lab results. So using serialised arrays for storing arbitrary data is not viable.
5. not all lab-results have a single data point. take, for example, blood pressure. for that you must record the diastolic, systolic and pulse. For urine analysis you need both the value of the measurement (e.g. norepinephrine) and the quantity of urine excreted. The number of data points is different for each lab-test.
6. the lab-test must also have the reference range available for them (if they are not supplied by the lab or entered by the user). Reference ranges are often a simple minimum and maximum; but are sometimes only ascertainable by an analysis of the person's gender and age (and sometimes other factors).
7. there are potentially hundreds of lab-tests that must be mapped.
8. i'm doing this for free so want to have an easy life.

I'm coming up with nothing when trying to deal with 1-8 inclusive. I have not come up with a decent paradigm to handle everything at once.

The choice at the moment seems to be:

A. a different table and a different class for each type of reading.
B. a single base class for readings and for those with 'oddities' a class that can inherit from the base PLUS a single table that has multiple columns (say four) for the measurement data

An alternative possibility is to adopt a wordpress-stype options table where the measurement values are stored in a key-value pair in a three column table

[ignore]
[table readings]
readingID,
userID,
readingDate,
readingTypeID,
labRefMin,
labRefMax,
readingLabels //(e.g. diastolic, systolic, pulse) - serialised array

[table readingTypes]
readingTypeID,
readingType //(e.g. Serum Na+, Serum K+, Serum Cl-)

[table readingValues]
readingID,
readingName,
readingValue
[/ignore]

Does anyone have any thoughts on what might be the most useful model? possibly a hybrid of B + alternative may be the best?

tia,
Justin

 

[jpadie]

with apologies - the readingLabels column should be in the readingTypes field. my bad.

 

[spamjim]

I'm sure you know the trade offs. Option "A" could offer more efficient queries but require more PHP development. Option "B" (and the Wordpress type table) could offer more efficient PHP development but clunkier queries.

Do you have a coin to flip?

 

[jpadie]

@spamjim

indeed I do know the trade-offs. but was so far deep into the design that i couldn't see the answer.

I started refactoring using a hybrid solution. so there will be one class to handle all measurements and one for all measurement values to be recorded for each measurement. so taking bp as a paradigm
[ignore]
[class measurement]
properties:
measurementID,
measurementDate,
measurementTypeID (fk to measurementType table)
userID,
labRefMin,
labRefMax

[class measurementValue]
properties:
measurementID,
measurementLabel,
measurementValue
[/ignore]

this has an impact on performance which may become problematic. But at the moment there will only be a couple of thousand active users and I am hoping that with sensible indexing (measurementID + measurementLabel, measurementLabel separately) and judicious cacheing I think we will be ok ...

The trade off on the ability to analyse statistics is unfortunate as I am led to understand that this database will be very useful to researchers in the relevant fields (seemingly there is no repository out there that is patient led with volunteered information). so I guess the answer is to see what these beardies want by way of analytics and write customised reports. If that has a material impact on performance then I suppose adding a stats table or even a stats server that takes a snapshot and reshapes the data in a daily batch job will not be too terrible.

Against all of this I remember talking at length with one of my clients in the aviation space about this exact conundrum. their app was built by Lithuanian developers (relevant only because there was a language gap and the developers were not in the business) and done so to a budget. It was developed to a functional spec and a design spec; but not a non-functional spec. Everyone was keen for it to be sorted quickly rather than planned carefully. So, for example, there was no log taken of logins so no method of analysing even basic user activity (that did not result in a non-idempotent action). The billing and rating engine allowed for only one product and one price point etc etc. And when I first ran an analytics report for some investors it brought the site down as the data structure was so sub-optimal. As a result i got a separate server for them in the same server-farm, set up an ssh tunnel to take a snapshot of the database and then reshaped the data. Doing so was about 15 minutes per day (the database became very large); and query times reduced from tens of minutes to a few seconds. but the whole process sucked up so much of my time that it would not have been profitable (although I was doing it for free as always).