I'm relatively new to databases so I apologize if there's an obvious way to approach this or if there is some fundamental process I'm missing. I'm using PHP and MySQL in a web application involving patient medical records. One requirement is that users be able to view and edit the medical records from a web page.
As I envisage it, a single Patient object has basic attributes like id, name, and address, and then each Patient also has an array of Medication objects (med_name, dose, reason), Condition objects (cond_name, date, notes), and other such objects (allergies, family history, etc.). My first thought was to have a database schema with tables as follows:
However, this seems wrong to me. Adding new medications or conditions is easy enough, but deleting or editing existing medications or conditions seems ridiculously inefficient - I'd have to, say, search through the medications table for a row matching patient_id with the old med_name, dose, and reason fields, and then delete/edit it with the new data. I could add some primary key to the medications and conditions tables to make it more efficient to find the row to edit, but that would seem like an arbitrary piece of data.
So what if I just had a single table with the following schema?
Where meds and conds are simply representations (say, binary) of arrays of Medication and Condition objects? PHP can interpret this data and fetch and update it in the database as needed.
Any thoughts on best practices here would be welcome. I'm also considering switching to Ruby on Rails, so if that affects any decisions I should make I'm interested to hear that as well. Thanks a lot folks.
The 'badness' or 'goodness' of encoding your data like that depends on your needs. If you NEVER need to refer to individual smaller chunks of data in those 'meds' and 'conds' tables, then there's no problem.
However, then you're essentially reducing your database to a slightly-smarter-than-dumb storage system, and lose the benefits of the 'relational' part of SQL databases.
e.g. if you ever need to run a a query for "find all patients who are taking viagra and have heart conditions", then the DBMS won't be able directly run that query, as it has no idea how you've "hidden" the viagra/heart condition data inside those two fields, whereas with a properly normalized database you'd have:
SELECT ...
FROM patients
LEFT JOIN conditions ON patients.id = conditions.patient_id
LEFT JOIN meds ON patients.id = meds.patient_id
WHERE (meds.name = 'Viagra') AND (condition.name = 'Heart Disease')
and the DBMS hands everything automatically. If you're encoding everything into a single field, then you're stuck with substring operations (assuming the data's in some readable ascii format), or at worse, having to suck the entire database across to your client app, decode each field, check its contents, then throw away everything that doesn't contain viagra or heart disease - highly inefficient.
This breaks first normal form. You can never query on object attributes that way.
I'd recommend either an ORM solution, if you have objects, or an object database.
I'd have to, say, search through the medications table for a row matching patient_id with the old med_name, dose, and reason fields, and then delete/edit it with the new data.
Assuming the key was {patient_id, med_name, start_date}, you'd just do a single update. No searching.
update medications
set reason = 'Your newly edited reason, for example.'
where patient_id = ?
and med_name = ?
and start_date = ?
Your app will already know the patient id, med name, and start date, because the user will have to somehow "select" the row those are in before any change will make sense.
If you're going to change the dosage, you need two changes, an update and an insert, in order to make sense.
update medications
set stop_date = '2012-01-12'
where patient_id = ?
and med_name = ?
and start_date = ?
-- I'm using fake data in this one.
insert into medications (patient_id, med_name, start_date, stop_date, dosage)
values (1, 'that same med', '2012-01-12', '2012-01-22', '40mg bid')