CODEWITHSUNDEEP
phpmysqlcakephpdatabase-schema
samJL
samJL

Reputation: 737

Best MySQL schema for an app that requires dynamically-built forms?

I am making a web application for a company that records data in the field.

They need to be able to create their own forms that the technicians will then fill out in the field at the job sites. Each job site is different from the next, hence the need for the company to be able to create new forms at will.

The data can be textual or numeric, and will be grid-like – comparing different equipment, for example:

Report #234 for Job Site 1

            EquipmentA  EquipmentB  EquipmentC
Condition   Fair        Good        Excellent
AZ425 Level 100 (ml)    84 (ml)     75 (ml)
Runtime     134 (hr)    400 (hr)    50 (hr)
Comment     Supply XX is running low.

============================

Report #3 for Job Site 2

            TankA       TankB
G00 Level   40%         10%     
Weight      500 (kg)    1000 (kg)   
pH          8.12        1.35
Visual Ins  Tank A has a crack near the bottom

I plan on using CakePHP/MySQL

What would be the best MySQL schema for this situation (or would XML make more sense)?

This is what I am working with so far:

Table 'Forms'
Cols: id, ref_number, job_site (fk), description, creation_date

Table 'Form_Controls'
Cols: id, form_id (fk), type (text, numeric, select), options (serialized data for dropdown list if a type 'select'), metric (hour, ml, kg, etc)

Table 'Form_Instance'
Cols: id, form_id (fk), technician (fk), creation_date

Table 'Form_Data'
Cols: id, control_id (fk), instance_id (fk), value, comparator (EquipmentA / EquipmentB, etc)

Is this the most efficient schema for this situation?

Upvotes: 3

Views: 266

Answers (2)

Jay Sidri
Jay Sidri

Reputation: 6406

It looks like Entity-Attribute-Value pattern might come in handy here. It can be easily modeled in a DB like MySQL

Upvotes: 2

APC
APC

Reputation: 146239

This sounds like the sort of thing where a key-value pair or a document-oriented datastore might be better. There are many so-called NoSQL databases to choose from but perhaps MongoDB might be a good place to start.

Upvotes: 0

Related Questions