将面向对象的数据直接编码到关系数据库中的单个行中被认为是不好的形式吗？

Question

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:

• patients (id, name, address, ...)
• medications ( patient_id, med_name, dose, reason)
• conditions ( patient_id, cond_name, date, notes)
• ...

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?

• patients (id, name, address, meds, conds, ...)

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.

Answer 1

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.