I am relatively recent to databases and so i apologize if there's an apparent method to approach this or maybe there's some rudimentary process I am missing. I am using PHP and MySQL inside a web application including patient medical records. One requirement is the fact that customers have the ability to view and edit the medical records from the web site.

When I envisage it, just one Patient object has fundamental characteristics like id, name, and address, after which each Patient also offers a range of Medication objects (med_name, dose, reason), Condition objects (cond_name, date, notes), along with other such objects (allergic reactions, genealogy, etc.). My first thought was to possess a database schema with tables the following:

  • patients (id, title, address, ...)
  • medicines ( patient_id, mediterranean_title, dose, reason)
  • conditions ( patient_id, cond_title, date, notes)
  • ...

However, this appears wrong in my experience. Adding new medicines or conditions is simple enough, but removing or editing existing medicines or conditions appears absurdly inefficient - I'd need to, say, sort through the medications table for any row matching patient_id using the old med_name, dose, and reason fields, after which remove/edit it using the new data. I possibly could then add primary answer to the medications and conditions tables to really make it more effective to obtain the row to edit, but that will appear as an arbitrary bit of data.

What exactly basically just were built with a single table using the following schema?

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

Where meds and conds are merely representations (say, binary) of arrays of Medication and Condition objects? PHP can interpret this data and fetch increase it within the database when needed.

Any ideas on guidelines here could be welcome. I am also thinking about switching to Ruby on Rails, therefore if that affects any choices I ought to make I am interested to listen to that a lot. Thanks folks.

The 'badness' or 'goodness' of encoding your computer data like this is dependent in your needs. Should you NEVER need to consult individual more compact portions of information in individuals 'meds' and 'conds' tables, plus there is not a problem.

However, then you are basically lowering your database to some slightly-wiser-than-dumb storage system, and lose the advantages of the 'relational' a part of SQL databases.

e.g. should you ever have to operate a a question for "find all patients who're taking the blue pill and also have heart diseaseInch, then your DBMS will not have the ability directly run that question, because it has no clue how you've "hidden" the the blue pill/heart problem data inside individuals two fields, whereas having a correctly stabilized 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 also the DBMS hands everything instantly. If you are encoding everything right into a single area, then you are tied to substring procedures (presuming the data's in certain readable ascii format), or at worse, needing to suck the whole database across for your client application, decode each area, check its contents, then discard exactly what does not contain the blue pill or cardiovascular disease - highly inefficient.

This breaks first normal form. You cant ever query on object characteristics this way.

I'd recommend either an ORM solution, for those who have objects, or perhaps an object database.