Algorithm or MySQL Query Advice Needed

Question

I am in desperate need of either algorithm or query construction assistance.

We have a user-generated, flexible database that is created using a form builder we have created. The data for these forms is stored in two tables as follows:

The instances table tell us what form the user is viewing, and then the instance_records table has all the data for the instance. The field_id column tells us what field on the form the data maps to. The reason we use a single table like this instead of creating a table for each form is that MySQL limits how many columns we can have in table given that the data is varchar of a significant length. One possibility would be to use Text fields for the data, but then we would lose the built in MySQL searching capabilities.

Things work quite well and very fast on basic forms. The problem is that one instance of a form can refer to another instance of the form. For example, we have user created form called Appointments. On this form, it refers to the Patient form, the Technician form, the Doctor form, etc.

So, on the Appointment form with instance id, the value for the patient field is actually an instance id of a patient, the doctor field value is the instance id for the doctor, etc. At the first level of references, things aren’t too bad. But, you can have chains of references. I can have a prescription that refers to an appointment that refers to a patient, etc. So, if I want to get the value of the patient name on the prescription, I have to follow the chain down to get the right instance id and field id for the data.

So, if I want to do a report on Appointments and show the Patient name, the Doctor name, and the Technician name, I have to go through some hoops. What I have tried is creating views and then joining the views to a final view that shows all the data for the query. But, it eats a ton of memory and starts writing the view temporary tables to disk and gets slow as all heck. Using query caching, the second time the report runs, it’s fast as heck. But, that first run can take over a minute once we get above 5000-7000 instances.

Something tickling at the back of my mind is that there might be some sort of a way to store the data in a way that I can take advantage of some faster tree search algorithms.

Answer 1

It sounds like you're trying to create a database in a database. There's a dailywtf link I'm looking for somewhere...

Anyway, it sounds like you need an Appointment table, and a Patient table, and a Doctor table, and a Technician table, and then you need to join them properly.

For example, to see the patients, doctors, and techs from the appointments yesterday, you might do

SELECT 
  Appointment.start-time 
  Appointment.end-time 
  Patient.name 
  Patient.insurance-carrier 
  Doctor.name 
  Tech.name 
  Tech.home-lab
FROM Appointment
JOIN Patient on Appointment.patient-id = Patient.patient-id
JOIN Doctor on Appointment.doctor-id = Doctor.doctor-id
JOIN Tech on Appointment.tech-id = Tech.tech-id
WHERE Appointment.appointment-date = $YESTERDAY

Edit: Let's give the example of Patient with a variable number of fields

Table Patient - contains data ALL patients will have

| ID   |    Name     | Insurance Carrier | .. other fields
+------+-------------+-------------------+-------
+ 0001 | John Doe    | ABC Healthcare    |
+ 0002 | Jane Doe    | ABC Healthcare    |
+ 0003 | Jon Skeet   | C# Insurance Inc. |
+ 0004 | Mark Byers  | Gold Badge Health |
+------+-------------+-------------------+-------

Table Patient-Form

| Form-Name |    Form-Field    | Required | Default-Value |
+-----------+------------------+----------+---------------|
| Vitals    | Blood Pressure   | TRUE     | null          |
| Vitals    | Pulse            | TRUE     | null          |
| Vitals    | Ear Temperature  | FALSE    | null          |
| Lab Work  | Lab Room         | TRUE     | Lab-001       |
| Lab Work  | Technician       | TRUE     | null          |
| Lab Work  | Insurance Covers | TRUE     | NO            |
| Payment   | Balance          | TRUE     | $0.00         |
| Payment   | Co-Pay           | FALSE    | 0.00%         |
| Payment   | Deductable       | FALSE    | $0.00         |
| Payment   | Payment Terms    | FALSE    | 30 Days Full  |
+-----------+------------------+----------+---------------|

Table Patient-Form-Field - contains data that may or may not be available for a patient

| Patient-ID | Form-Name |    Form-Field    | Form Value |
+------------+-----------+------------------+------------+
+ 0001       | Vitals    | Blood Pressure   | 130 / 54   |
+ 0001       | Vitals    | Pulse            | 84bpm      |
+ 0001       | Vitals    | Ear Temperature  | 98.4F      |
+ 0002       | Vitals    | Blood Pressure   | 126 / 74   |
+ 0002       | Vitals    | Pulse            | 87bpm      |
+ 0002       | Vitals    | Ear Temperature  | 99.0F      |
+ 0003       | Lab Work  | Lab Room         | SO-Meta    |
+ 0003       | Lab Work  | Technician       | Rose Smith |
+ 0003       | Lab Work  | Insurance Covers | TRUE       |
+ 0003       | Vitals    | Blood Pressure   | 190 / 100  |
+ 0003       | Vitals    | Pulse            | 213bpm     |
+------------+-----------+------------------+------------+

You can now query this like this:

SELECT 
  Patient.name 
  Patient-form-field.form-name 
  Patient-form-field.form-field 
  Patient-form-field.form-value
FROM Patient
JOIN Patient-Form-Field on ( Patient.patient-id = patient.id 
                         AND Patient-form-field in ("Vitals","Lab Work")
                           )
WHERE Patient.patient-id IN ("0001","0002","0003")

Answer 2

You should read up on EAV... This article might give you some ideas... It talks about two different approaches for storing values. Either approach you end up having a single query for any given form that would essentially grab all the values for the master entity (in this case the form). Then either on the application side or the db side you aggregate those values together appropriately for the application to consume.

The form itself should be a single atomic unit that has a list of fields, you dont need to store which form a field actually comes from you just need to store it as a field on the complete form. You should develop logic for merging the fields to a single form on the application side during the creation process.