Database Structure: Difference between revisions

From IHRIS Wiki
No edit summary
 
(4 intermediate revisions by 3 users not shown)
Line 1: Line 1:
iHRIS utiliza varias tablas en una base de datos relacional (MySQL) para guardar sus datos. Este artículo describe varias de las tablas que utiliza iHRIS, en particular la tabla del usuario y las tablas que se utilizan para [[Form Storage -- Entry/Last Entry | cambios de datos de formularios auditados]].
iHRIS makes use of multiple tables in a relational database (MySQL) to store its data in. This article describes several of the tables used by iHRIS, in particular the user table and the tables used for [[Form Storage -- Entry/Last Entry | audited form data changes]].


'''Nota: Este documento esta desactualizado y se ha marcado para revisión.'''
'''Note: This document maybe out of date.'''




La base de datos de iHRIS se ha abstraído para que la estructura del objeto se pueda manejar con formularios dentro del sitio. Todos los registros que se guardan para un sitio son una instancia de un Formulario I2CE_Form. Esto define todos los campos que se utilizaron para ese formularioLos nombres de formularios y campos se guardan en el formulario y los campos de las tablas en la sección de [[#Form Definitions| definiciones de formularios]] a continuación. Estas se combinan en la tabla form_field para mapear los campos asociados con un formulario dado.
The iHRIS Database is abstracted so the object structure can be handled by forms within the site. All records saved for a site are an instance of I2CE_Form. This defines all the fields used for that formThe form and field names are saved in the form and field tables in the [[#Form Definitions|form definitions]] section below. These are combined in the form_field table to map all the fields associated with a given form.


Los datos para cada formulario se guardan como un registro (vea [[#Record Tables|Tablas de Registro]]).  Cada campo luego se guarda en las tablas de entry y last_entry.  La tabla entry guarda un historial de todos los cambios y la tabla de last_entry es una búsqueda rápida del valor actual. De modo que si tiene una instancia de un formulario con 2 campos, habrá 1 fila guardada en la tabla de registro y 2 filas en entry y last_entry.  Siempre habrá solamente 3 filas en la tabla last_entry pero las filas de la tabla de entrada aumentarán cada vez que se realice un cambio.
The data for each form is saved as a record (see [[#Record Tables|Record Tables]]).  Each field is then saved in the entry and last_entry tablesThe entry table keeps a history of all changes and the last_entry table is a quick lookup for the current value. So if you have an instance of a form with 2 fields there will be 1 row saved to the record table and 2 rows each in entry and last_entry.  There will always only be 2 rows in the last_entry table but the rows in the entry table will increase each time a change is made.


== Tablas de Usuarios ==
== User Tables ==
Las tablas de usuarios pueden estar en la misma base de datos principal que el resto del sitio, o pueden estar en una base de datos compartida para que los nombres y las contraseñas puedan utilizarse más de una vez. La tabla de acceso debe estar en la base de datos principal para permitir acceso al sitio.
The user tables can be in the same main database as the rest of the site, or they can be in a shared database so names and passwords
can be reused. The access table must be in the main database to give access for the site.
{|border="1" cellspacing="0" cellpadding="5"
{|border="1" cellspacing="0" cellpadding="5"
!Tabla
!Table
!Descripción
!Description
!Definición de la Tabla
!Table Definition
|-
|-
!user
!user
|La tabla de user tiene la información de contacto y de acceso de todos los usuarios del sistema. La contraseña está cifrada con MD5() y la id se referencia en cualquier otra tabla que se refiere al usuario. El nombre de usuario y la id deben ser únicos. Se utilizan el nombre y apellido para efectos de visualización. La dirección de correo se utiliza para contactar al usuario para cambios de contraseña o contraseñas olvidadas. El creador es una referencia a la id de usuario que creó este usuario.
|The user table holds the login and contact information for all users on the system. The password is encrypted with MD5() and the id is referenced in any other tables referring to the user. The username and id must be unique. The firstname and lastname are used for display purposes. The email address is used to contact the user for password changes or forgotten passwords. The creator is a reference to the user id that created this user.
|<pre>
|<pre>
CREATE TABLE `user` (
CREATE TABLE `user` (
Line 32: Line 33:
|-
|-
!user_log
!user_log
|La tabla de user_log tiene los datos de inicio y finalización de sesión de los usuarios. También vincula el inicio de sesión a la session_id que está asociada con el usuario.
|The user_log table holds login and logout data for users. It also ties the login to the session_id associated with the user.
|<pre>
|<pre>
CREATE TABLE user_log (
CREATE TABLE user_log (
Line 46: Line 47:
|-
|-
!access
!access
|La tabla de access controla el acceso de los usuarios al sitio. El rol es una cadena definida por el sitio para controlar las áreas que puede ver el usuario y que acciones puede realizar.
|The access table controls access for the users to the site. The role is a string defined by the site to control what areas the user can see and what actions can be performed.
|<pre>
|<pre>
CREATE TABLE access (
CREATE TABLE access (
Line 57: Line 58:
|}
|}


== Definiciones del Formulario ==
== Form Definitions ==
Estas tablas definen los formularios y campos asociados con el sitio.
These tables define the forms and fields associated with the site.
{|border="1" cellspacing="0" cellpadding="5"
{|border="1" cellspacing="0" cellpadding="5"
!Tabla
!Table
!Descripción
!Description
!Definición de la Tabla
!Table Definition
|-
|-
!form
!form
|La tabla de form define un nombre corto para un formulario y lo vincula a un id único. El campo del tipo se omite.
|The form table defines a short name for a form and links it to a unique id. The type field is deprecated.
|<pre>
|<pre>
CREATE TABLE form (
CREATE TABLE form (
Line 77: Line 78:
|-
|-
!field
!field
|La tabla de field define un nombre corto para todos los campos que se utilizan en el sitio. El tipo es el tipo de datos para el campo dado.
|The field table defines a short name for all the fields used in the site. The type is the data type for the given field.
|<pre>
|<pre>
CREATE TABLE field (
CREATE TABLE field (
Line 89: Line 90:
|-
|-
!form_field
!form_field
|La tabla de form_field mapea una lista de campos que se asocian con el formulario dado. Todos los datos que se guarden estarán entonces asociados con el id único del form_field.
|The form_field table maps a list of fields that are associated with the given form. Any saved data will then be associated with the unique id of the form_field.
|<pre>
|<pre>
CREATE TABLE form_field (
CREATE TABLE form_field (
Line 101: Line 102:
|-
|-
|}
|}
== Tablas de Registro ==
== Record Tables ==
Las tablas de registro guardan información específica que se ha guardado para cada formulario asociado con el sitio.
The record tables store specific information saved for each form associated with the site.
{|border="1" cellspacing="0" cellpadding="5"
{|border="1" cellspacing="0" cellpadding="5"
!Tabla
!Table
!Descripción
!Description
!Definición de la Tabla
!Table Definition
|-
|-
!record
!record
|La tabla record es la tabla principal asociada con cada instancia de un formulario. Hay una id única para su fácil referencia. El campo last_modified se actualiza cada vez que se le hace un cambio al registro dado. El formulario es la id del formulario del que este registro es una instancia. Si el registro tiene un registro primario, entonces el campo primario se llenará con esa id de registro.
|The record table is the main table associated with each instance of a form. There is a unique id for easy reference. The last_modified field is updated every time a change is made to the given record. The form is the id of the form this record is an instance of. If the record has a parent record then the parent field will be populated with that record id.
|<pre>
|<pre>
CREATE TABLE record (
CREATE TABLE record (
Line 123: Line 124:
!entry
!entry
last_entry
last_entry
|Las tablas de entry and last_entry son muy similares. La tabla de entrada lleva un registro de todos los cambios realizados al valor de un form_field dado para un registroLa last_entry mantiene la última entrada para su fácil acceso. El registro es la id del registro con el que se asocia este valor para el form_field dadoLa fecha es la fecha en que se guardó este valor.  Who es la user id de la persona que realizó esta modificación. El change_type se establece dependiendo de si esta es una entrada inicial, una corrección o una actualización regular de este valor. También se puede establecer para que se verifique si los datos se han revisado. Uno de los campos de valores se llenará en base al tipo de form_field.
|The entry and last_entry tables are very similar. The entry table holds a record of all changes made to a given form_field value for a recordThe last_entry holds the latest entry for quicker access. The record is the id of the record this value is associated with for the given form_field.  The date is the date this value was saved.  Who is the user id of the person who made this entry. The change_type is set depending on if this is an initial entry, a correction or a regular update to this value. It can also be set to verified if the data has been double checked. One of the value fields will be populated based on the type of the form_field.
|<pre>
|<pre>
CREATE TABLE entry (
CREATE TABLE entry (
Line 160: Line 161:
|-
|-
!field_sequence
!field_sequence
|La tabla de field_sequence se utiliza para llevar registro de un valor entero para un form_field que el sitio generará y aumentará automáticamente. Lleva registro del último valor utilizado para el form_field dado.
|The field_sequence table is used to track an integer value for a form_field that will be automatically generated and incremented by the site. It keeps track of the last value used for the given form_field.
|<pre>
|<pre>
CREATE TABLE field_sequence (
CREATE TABLE field_sequence (
Line 170: Line 171:
|-
|-
!deleted_record
!deleted_record
|El deleted_record se utiliza para guardar registros que se borran del sistema en caso que deban recuperarse. Es un espejo de la tabla de registro.
|The deleted_record is used to save records that are removed from the system in case it needs to be recovered. It is a mirror of the record table.
|<pre>
|<pre>
CREATE TABLE deleted_record (
CREATE TABLE deleted_record (
Line 184: Line 185:
|}
|}


== Tablas de Utilidades ==
== Utility Tables ==
{|border="1" cellspacing="0" cellpadding="5"
{|border="1" cellspacing="0" cellpadding="5"
!Tabla
!Table
!Descripción
!Description
!Definición de la Tabla
!Table Definition
|-
|-
!config
!config
|La tabla config guarda todos los datos de configuración para el sitio. Estos datos se leen de los archivos XML de configuración para los módulos. El hash es un hash MD5 de la ruta. Se utiliza para realizar búsquedas de claves únicas. Se comparte con el hash que está guardado en el APC. La ruta es un formato legible de la ruta hacia los datos. El tipo determina si esta entrada es un nodo primario o final. Si es un nodo final, el valor se establecerá con el valor del nodo. Si es un primario, entonces los secundarios se establecerán con una lista de nodos secundarios para esta entrada.
|The config table stores all the configuration data for the site. This data is read from the configuration XML files for modules. The hash is a MD5 hash of the path. It is used for unique key look ups. It is shared with the hash that is stored in the APC. The path is a readable format of the path to the data. The type determines if this entry is a parent or an end node. If an end node then the value will be set with the value for the node. If it's a parent then children will be set with a list of children nodes for this entry.
|<pre>
|<pre>
CREATE TABLE config (
CREATE TABLE config (
Line 204: Line 205:
|-
|-
!report_list
!report_list
|La tabla de report_list es simplemente una definición temporal para crear tablas temporales al crear un reporte en caché. Tiene registros primaries y secundarios que se guardarán en dependencia del reporte que se este realizando en caché.
|The report_list table is simply a place holder definition to create temporary tables when creating a cached report. It has primary and secondary records that will be saved depending on the report being cached.
|<pre>
|<pre>
CREATE TABLE report_list (
CREATE TABLE report_list (
Line 215: Line 216:
|}
|}


== Ejemplo de Formulario ==
== Form Example ==


Este es un ejemplo de como se guardarían dos formularios en la base de datos. El formulario de persona tiene un campo para el surname (apellido) y el formulario demogáfico tiene un campo de birth_date. El formulario de persona se guardaría primaro ya que es el formulario primario. Asumiendo que nunca se ha guardado ningún formulario en la base de datos, lo siguiente ocurriría al guardarlo.
This is an example of how two forms would be saved to the database. The person form has a surname field and the demographic form has a birth_date field. The person form would be saved first since it is the parent form. Assuming no forms have ever been saved to the database the following would happen on saving.


# Cree las entradas para '''form''', '''field''' y '''form_field'''.
# Create the '''form''', '''field''' and '''form_field''' entries.
## Se agregará una entrada a la tabla de '''form''' con el ''name'' para "person."  Esto asignará automaticamente una ''id'' de formulario de 1 ya que es el primero.
## An entry is added to the '''form''' table with the ''name'' being "person."  This will automatically assign a form ''id'' of 1 since it's the first one.
## Se agregará una entrada a la tabla de '''field''' con el ''name'' para "surname."  Esto asignará automaticamente una ''id'' de campo de 1.
## An entry is added to the '''field''' table with the ''name'' being "surname."  This will automatically assign a field ''id'' of 1.
## Se agregará una entrada a la tabla de '''form_field''' con el ''form'' de 1 (para persona) y el ''field'' 1 (para el apellido).  Esto asignará automaticamente una ''id'' para el form_field de 1.
## An entry is added to the '''form_field''' table with the ''form'' being 1 (for person) and the ''field'' being 1 (for surname).  This will automatically assign a form_field ''id'' of 1.
## Se agregará una entrada a la tabla de '''form''' con el ''name'' para "demographic."  Esto asignará automaticamente una ''id'' de formulario de 2 ya que es la primera.
## An entry is added to the '''form''' table with the ''name'' being "demographic."  This will automatically assign a form ''id'' of 2 since it's the first one.
## Se agregará una entrada a la tabla de '''field''' con el ''name'' para "birth_date."  Esto asignará automaticamente una ''id'' de campo de 2.
## An entry is added to the '''field''' table with the ''name'' being "birth_date."  This will automatically assign a field ''id'' of 2.
## Se agregará una entrada a la tabla de '''form_field''' con el ''form'' para 2 (para demographic) y el ''field'' para 2 (para birth_date).  Esto asignará automaticamente una ''id'' para el form_field de 2.
## An entry is added to the '''form_field''' table with the ''form'' being 2 (for demographic) and the ''field'' being 2 (for birth_date).  This will automatically assign a form_field ''id'' of 2.
# Cree el registro de la persona.
# Create the person record.
## Se agregará un nuevo registro a la tabla de '''record'''. La ''id'' de registro se generará automaticamente (1) y ''form'' se establecerá como 1.  No hay ''primario'' y la hora de ''last_modified'' se establecerá como la hora actual.
## A new record will be added to the '''record''' table. The record ''id'' will be generated automatically (1) and the ''form'' will be set to 1.  There is no ''parent'' and the ''last_modified'' time will be set to the current time.
## Se agregará una entrada a las tablas de '''entry''' y '''last_entry'''. El ''record'' se establecerá como 1 y el ''form_field'' se establecerá como 1 ( la id de form_field creada anteriormente para la persona-apellido). ''Date'' será la hora actual y ''who'' se establecerá como la id de usuario que esta realizando el cambioEl campo ''string_value'' se establecerá como el valor del apellido.
## An entry will be added to the '''entry''' and '''last_entry''' tables. The ''record'' will be set to 1 and the ''form_field'' will be set to 1 (the form_field id created above for person-surname). The ''date'' will be the current time and ''who'' will be set to the user id making the changeThe ''string_value'' field will be set to the value for the surname.
# Cree el registro demográfico.
# Create the demographic record.
## Se agregará un Nuevo registro a la tabla de '''record''' . La ''id'' del registro se generará automaticamente (2) y el ''form'' se establecerá como 2.  El ''primario'' se establecerá como 1 ya que este es un formulario secundario del registro de persona que se acaba de crearLa hora de ''last_modified'' se establecerá como la hora actual.
## A new record will be added to the '''record''' table. The record ''id'' will be generated automatically (2) and the ''form'' will be set to 2.  The ''parent'' will be set to 1 since this is a child form for the person record that was just createdThe ''last_modified'' time will be set to the current time.
## Se agregará una entrada a las tablas '''entry''' y '''last_entry'''.  El ''record'' se establecerá como 2 y el ''form_field'' se establecerá como 2 (la id de form_field creada anteriormente para demographic-birth_date).  La ''fecha'' sera la hora actual y ''who'' se establecerá como la id de usuario que esta haciendo el cambioEl campo ''date_value'' se establecerá al valor para birth_date.
## An entry will be added to the '''entry''' and '''last_entry''' tablesThe ''record'' will be set to 2 and the ''form_field'' will be set to 2 (the form_field id created above for demographic-birth_date).  The ''date'' will be the current time and ''who'' will be set to the user id making the changeThe ''date_value'' field will be set to the value for the birth_date.
[[Category:Technical Overview]][[Category:Database]][[Category:Review2013]][[Category:Needs Intro]]
 
[[Category:Developer Resources]]

Latest revision as of 21:36, 1 March 2019

iHRIS makes use of multiple tables in a relational database (MySQL) to store its data in. This article describes several of the tables used by iHRIS, in particular the user table and the tables used for audited form data changes.

Note: This document maybe out of date.


The iHRIS Database is abstracted so the object structure can be handled by forms within the site. All records saved for a site are an instance of I2CE_Form. This defines all the fields used for that form. The form and field names are saved in the form and field tables in the form definitions section below. These are combined in the form_field table to map all the fields associated with a given form.

The data for each form is saved as a record (see Record Tables). Each field is then saved in the entry and last_entry tables. The entry table keeps a history of all changes and the last_entry table is a quick lookup for the current value. So if you have an instance of a form with 2 fields there will be 1 row saved to the record table and 2 rows each in entry and last_entry. There will always only be 2 rows in the last_entry table but the rows in the entry table will increase each time a change is made.

User Tables

The user tables can be in the same main database as the rest of the site, or they can be in a shared database so names and passwords can be reused. The access table must be in the main database to give access for the site.

Table Description Table Definition
user The user table holds the login and contact information for all users on the system. The password is encrypted with MD5() and the id is referenced in any other tables referring to the user. The username and id must be unique. The firstname and lastname are used for display purposes. The email address is used to contact the user for password changes or forgotten passwords. The creator is a reference to the user id that created this user.
CREATE TABLE `user` (
  id int(11) NOT NULL auto_increment,
  username varchar(20) NOT NULL,
  `password` varchar(50) NOT NULL,
  firstname varchar(50) NOT NULL,
  lastname varchar(100) NOT NULL,
  email varchar(100) default NULL,
  creator int(11) NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY username (username)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
user_log The user_log table holds login and logout data for users. It also ties the login to the session_id associated with the user.
CREATE TABLE user_log (
  `user` int(11) NOT NULL,
  login datetime NOT NULL,
  logout datetime default NULL,
  session_id varchar(50) NOT NULL,
  activity datetime NOT NULL,
  KEY `user` (`user`),
  KEY login (login)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
access The access table controls access for the users to the site. The role is a string defined by the site to control what areas the user can see and what actions can be performed.
CREATE TABLE access (
  `user` int(11) NOT NULL,
  role varchar(255) collate utf8_bin NOT NULL,
  PRIMARY KEY  (`user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Form Definitions

These tables define the forms and fields associated with the site.

Table Description Table Definition
form The form table defines a short name for a form and links it to a unique id. The type field is deprecated.
CREATE TABLE form (
  id int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) collate utf8_bin NOT NULL,
  `type` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
field The field table defines a short name for all the fields used in the site. The type is the data type for the given field.
CREATE TABLE field (
  id int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) collate utf8_bin NOT NULL,
  `type` varchar(16) collate utf8_bin NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY name_type (`name`,`type`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
form_field The form_field table maps a list of fields that are associated with the given form. Any saved data will then be associated with the unique id of the form_field.
CREATE TABLE form_field (
  id int(10) unsigned NOT NULL auto_increment,
  form int(10) unsigned NOT NULL,
  field int(10) unsigned NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY form (form,field)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Record Tables

The record tables store specific information saved for each form associated with the site.

Table Description Table Definition
record The record table is the main table associated with each instance of a form. There is a unique id for easy reference. The last_modified field is updated every time a change is made to the given record. The form is the id of the form this record is an instance of. If the record has a parent record then the parent field will be populated with that record id.
CREATE TABLE record (
  id int(10) unsigned NOT NULL auto_increment,
  last_modified datetime NOT NULL,
  form int(10) unsigned NOT NULL,
  parent int(10) unsigned default NULL,
  PRIMARY KEY  (id),
  KEY parent (parent)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
entry

last_entry

The entry and last_entry tables are very similar. The entry table holds a record of all changes made to a given form_field value for a record. The last_entry holds the latest entry for quicker access. The record is the id of the record this value is associated with for the given form_field. The date is the date this value was saved. Who is the user id of the person who made this entry. The change_type is set depending on if this is an initial entry, a correction or a regular update to this value. It can also be set to verified if the data has been double checked. One of the value fields will be populated based on the type of the form_field.
CREATE TABLE entry (
  record int(10) unsigned NOT NULL,
  form_field int(10) unsigned NOT NULL,
  `date` datetime NOT NULL,
  who int(10) unsigned NOT NULL,
  change_type tinyint(3) unsigned NOT NULL,
  string_value varchar(255) collate utf8_bin default NULL,
  integer_value int(11) default NULL,
  text_value text collate utf8_bin,
  date_value datetime default NULL,
  blob_value longblob,
  PRIMARY KEY  (record,form_field,`date`),
  KEY `date` (`date`),
  KEY form_field (form_field),
  KEY record (record)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE last_entry (
  record int(10) unsigned NOT NULL,
  form_field int(10) unsigned NOT NULL,
  `date` datetime NOT NULL,
  who int(10) unsigned NOT NULL,
  change_type tinyint(3) unsigned NOT NULL,
  string_value varchar(255) collate utf8_bin default NULL,
  integer_value int(11) default NULL,
  text_value text collate utf8_bin,
  date_value datetime default NULL,
  blob_value longblob,
  PRIMARY KEY  (record,form_field),
  KEY form_field (form_field),
  KEY record (record)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
field_sequence The field_sequence table is used to track an integer value for a form_field that will be automatically generated and incremented by the site. It keeps track of the last value used for the given form_field.
CREATE TABLE field_sequence (
  form_field int(11) NOT NULL,
  sequence int(11) unsigned NOT NULL,
  PRIMARY KEY  (form_field)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
deleted_record The deleted_record is used to save records that are removed from the system in case it needs to be recovered. It is a mirror of the record table.
CREATE TABLE deleted_record (
  id int(10) unsigned NOT NULL auto_increment,
  last_modified datetime NOT NULL,
  form int(10) unsigned NOT NULL,
  parent int(10) unsigned default NULL,
  PRIMARY KEY  (id),
  KEY parent (parent)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Utility Tables

Table Description Table Definition
config The config table stores all the configuration data for the site. This data is read from the configuration XML files for modules. The hash is a MD5 hash of the path. It is used for unique key look ups. It is shared with the hash that is stored in the APC. The path is a readable format of the path to the data. The type determines if this entry is a parent or an end node. If an end node then the value will be set with the value for the node. If it's a parent then children will be set with a list of children nodes for this entry.
CREATE TABLE config (
  `hash` char(32) character set latin1 NOT NULL,
  path varchar(10000) character set latin1 NOT NULL,
  `type` tinyint(4) NOT NULL,
  `value` varchar(2000) character set latin1 default NULL,
  children varchar(10000) character set latin1 default NULL,
  PRIMARY KEY  (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
report_list The report_list table is simply a place holder definition to create temporary tables when creating a cached report. It has primary and secondary records that will be saved depending on the report being cached.
CREATE TABLE report_list (
  `primary` int(11) NOT NULL,
  secondary int(11) NOT NULL,
  PRIMARY KEY  (`primary`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Form Example

This is an example of how two forms would be saved to the database. The person form has a surname field and the demographic form has a birth_date field. The person form would be saved first since it is the parent form. Assuming no forms have ever been saved to the database the following would happen on saving.

  1. Create the form, field and form_field entries.
    1. An entry is added to the form table with the name being "person." This will automatically assign a form id of 1 since it's the first one.
    2. An entry is added to the field table with the name being "surname." This will automatically assign a field id of 1.
    3. An entry is added to the form_field table with the form being 1 (for person) and the field being 1 (for surname). This will automatically assign a form_field id of 1.
    4. An entry is added to the form table with the name being "demographic." This will automatically assign a form id of 2 since it's the first one.
    5. An entry is added to the field table with the name being "birth_date." This will automatically assign a field id of 2.
    6. An entry is added to the form_field table with the form being 2 (for demographic) and the field being 2 (for birth_date). This will automatically assign a form_field id of 2.
  2. Create the person record.
    1. A new record will be added to the record table. The record id will be generated automatically (1) and the form will be set to 1. There is no parent and the last_modified time will be set to the current time.
    2. An entry will be added to the entry and last_entry tables. The record will be set to 1 and the form_field will be set to 1 (the form_field id created above for person-surname). The date will be the current time and who will be set to the user id making the change. The string_value field will be set to the value for the surname.
  3. Create the demographic record.
    1. A new record will be added to the record table. The record id will be generated automatically (2) and the form will be set to 2. The parent will be set to 1 since this is a child form for the person record that was just created. The last_modified time will be set to the current time.
    2. An entry will be added to the entry and last_entry tables. The record will be set to 2 and the form_field will be set to 2 (the form_field id created above for demographic-birth_date). The date will be the current time and who will be set to the user id making the change. The date_value field will be set to the value for the birth_date.