I have a database with the following table:
PATIENT (PATIENT_ID*, MEDICAL_EXAMINATIONS)
where the field MEDICAL_EXAMINATIONS contains a free-text description of the exams undertaken by the patient.
Recently, it was decided that medical examination can be reported EITHER as free-text (as always) OR in a structured way (divided in exam name, date, results, and so on).
So I thought to change the schema as follow (fields marked with an asterisk compose the key):
PATIENT (PATIENT_ID*, MEDICAL_EXAMINATIONS)
MEDICAL_EXAMINATION (PATIENT_ID*, NUMBER*, NAME, DATE, RESULT)
but I found this solution a little disturbing, because I have the SAME information (the medical examinations) stored in TWO tables. In this case, the result of the query "select all medical examinations undertaken by a patient" is not so "elegant".
I don't really know how to express my question, but this situation seems STRANGE to me. I wonder if the problem originates intrinsecally from the specifications (that I can't change) or if there is a better method to model the "two versions" of data.
Personally, I would seperate out the concept of medical examinations completely from the patient into two seperate tables, like so:
PATIENT(PATIENT_ID)
MEDICAL_EXAMINATION(PATIENT_ID,NAME,DATE,RESULT)
MEDICAL_EXAMINATION_NOTES(PATIENT_ID,NOTES)
"Notes" is a rough guess at the table name, there may be a more appropriate name for this based on what the use cases are.
This allows you some added flexibility as you could have multiple "free-form" examinations at some point in the future if you chose.
Selecting out both of these is always going to be troublesome as you have a different data strucuture. You'd probably be limited to the lowest-common-denominator and pull them out as strings if you wanted to get them together, like so:
SELECT 'Name ' + NAME + ', Date ' + DATE + ', Result: ' + RESULT AS EXAM
FROM MEDICAL_EXAMINATION WHERE PATIENT_ID = @PATIENT_ID
UNION ALL
SELECT NOTES AS EXAM FROM MEDICAL_EXAMINATION_NOTES WHERE PATIENT_ID = @PATIENT_ID
Better yet, if this database is backing some sort of business objects, have a seperate class for "free-form" and "structured" examinations and then a common interface that gives a string representation of the medical examination. That way your business layer has the option of treating them seperately or using them together.