Thanks for the question, Brian.
Asked: April 21, 2002 - 7:56 pm UTC
Answered by: Tom Kyte - Last updated: July 08, 2002 - 7:50 pm UTC
Category: Database - Version: 8.1.6
Viewed 1000+ times
Thanks for you answer my questions.
This is a question about clinical trial database design and performance.
Clinical patient records needs to store many possible facts, for examples, (patient XYZ 1/5/2001, 12:00AM 12.5 gm/dl, 4.9 Meq/L, 80 IU....)
EAV design represents a column to row transformation, like this:
Patient date time lab_test_name result
(patient XYZ 1/5/2001, 12:00AM 'Hemoglobin", 12.5gm/dl)
(patient XYZ 1/5/2001, 12:00AM 'Potassium" 4.9 Meq/L)
But it raises a problem: Query performance of a big clinical report. Because each attribute-value pair for a patient is stored as separate row in a table, when performing a similar complex Boolean query on a single EAV table, the conceptual AND, OR and NOT operations must be translated into the row-based operations of set union, set intersection and set differences...., and select statements involved in distinct, max, full outer join (missing data problem), self join, nested subqueries etc, which are more complexes, inefficient.
From your experience, what is the best model to store this kind of clinical data from database design and performance point of view? How does oracle handle this kind of issues?
and we said...
While the above is flexible, it is not scalable if you need to perform queries like "find the patients that have had test X and test Y or something_else"
If at all possible, I would use a model like:
The problem there may well be that you have more then say 900 observations -- we run out of columns.
What you might also consider would be XML (especially in 9i with some of the new features therein).
There you could create an XML document for the patient with their history and using Oracle Text (in 9i) or Intermedia (8i) you can index the XML document. So, instead of inserting a new observation, you would update an XML tagged document. Then you can do very efficient CONTAINS searching on that using the tagged information.
and you rated our response
Is this answer out of date? If it is, please let us know via a Review