Form Storage -- Entry/Last Entry

This is the default storage mechanism that a form uses

Features

This entry storage mechanism tracks changes made to the data by user and time.

Database Structure

This is a vertical database structure.

Entry and Last Entry

All data is stored in two structurally identical tables, entry and last_entry. The entry table contains all data saved into the system, while last_entry only saves the current version of the data. Each row of this table corresponds to one field of one instance of a form. These references are made by the columns record and form_field which are the id's of the tables record and form_field

Record

The record table has one row for each form instance. A form instance is the name of a form and an id.

The record table contains the columns id, form, parent_form and parent_id. The form column matches the id column in the form table and which tells us which form this record, or instance, is. The parent_form column is a string and tells us the name, if any, of a parent form for this form instance. Similarly, the parent_id tells us the id, if any, of the parent form. The parent form may or may not be saved into the entry table.

The id is referenced by the entry and last_entry tables

There is also a modification time which tracks the last time any field of this form instance was modified.

Form

The form table describe the forms which have been saved into the entry table. There are columns id and name. It's id is referenced by the form field table.

Field

The field table describe all of the fields and their database types. It contains the columns: id, name and type. There is one row for each pair (name of a field, field database type) which is assigned an id. The database type, tells us which column that the field data is saved in the last_entry and entry tables. It's id column is referenced by the form_field table.

Form Field

The form field table has on row for each pair of (form, field id). It's id column is referenced by the entry and last entry tables.

Diagram

<graphviz border='frame' format='png'>

digraph "Database Structure" {

  form [shape=record, label="table:form|{id|name}"]
  field [shape=record, label="table:field|{id|name}"]
  form_field [shape=record,label="table:form_field|{id|form|field}"]
  record [shape=record , label="table:record|{id}"]
  last_entry [shape=record,label="table:last_entry|{record|form_field|string_value|date_value|...}"]
  entry [shape=record,label="table:entry|{record|form_field|string_value|text_value|...}"]

  form_field->field [label="field=id"]
  form_field->form [label="form=id"]
  entry->form_field[label="form_field=id"]
  last_entry->form_field[label="form_field=id"]
  entry->record[label="record=id"]  
  last_entry->record[label="record=id"]    

} </graphviz>

Sample SQL Queries

Getting all the data for a form

Suppose you want to get all the fields for the 'person' form with id '10260.' This can be done by:

SELECT 
   field.name AS `Field`, 
   field.type AS `Type`,
   last_entry.integer_value AS `Integer`, 
   last_entry.string_value AS `String`, 
   last_entry.text_value AS `Text`, 
   last_entry.date_value AS `Date` 
FROM last_entry
JOIN form_field ON last_entry.form_field = form_field.id
JOIN field ON form_field.field = field.id
WHERE last_entry.record = '10260'
AND last_entry.form_field IN 
  (SELECT form_field.id FROM form_field 
  JOIN form on form_field.form = form.id
  WHERE form.name = 'person')