Helix Installation   Getting Started   Reference Documentation   Global Functions   Quick Start
Getting StartedHelpful ResourcesAsk questions on the Help ForumRead the User GuideBrowse the Reference DocumentationSubscribe to the Helix Mailing ListLearning HelixAdvanced Topics

Database Customization

All tables perform a particular function. Functions performed by tables may be data storage, relating multiple tables together, serving as lookup data and so on. Detailed descriptions of table types are outlined below. When designing a table, determine what function the table performs, select the correct type of table to structure properly, and use the guidelines below.

Data Table

Data tables hold actual values that are input/output by the application. Data tables are the primary building blocks of data storage. Data typically relate to objects built within applications.All data tables must have a the following columns:

  • id - Unique integer
  • mdate - datetime that stands for modified date
  • cdate - datetime that stands for create date
  • deleted - tinyint that specifies whether or not the record is marked as deleted
In addition to these required fields, fields that hold data relevant to a corresponding application object will exist here.

Example:


CREATE TABLE `cor_entity` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `description` TEXT,
  `entity_type_id` INT(11) DEFAULT 0,
  `mdate` DATETIME DEFAULT NULL,
  `cdate` DATETIME DEFAULT NULL,
  `deleted` TINYINT(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_cor_entity_type` FOREIGN KEY (`entity_type_id`) REFERENCES `cor_entity_type` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Relationship Table

The relationship table is simply a link between two data tables. All relationship tables must have the following columns:

  • id - Unique integer
  • tablename1_id - this is the functional identifier of the table on the left of the underscore in the tablename (alphabetically first), not the id of the table.
  • tablename2_id - this is the functional identifier of the table on the right of the underscore in the tablename (alphabetically second), not the id of the table.
  • mdate - datetime that stands for modified date
  • cdate - datetime that stands for create date
  • deleted - tinyint that specifies whether or not the record is marked as deleted
Relationship tables may have an optional order_id column if the relationship requires a specific order.

Example:


CREATE TABLE `cor_entity_phone` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `entity_id` INT(11) NOT NULL DEFAULT 0,
  `phone_id` INT(11) NOT NULL DEFAULT 0,
  `entity_phone_type_id` INT(11) DEFAULT 0,
  `primary` TINYINT(1) NOT NULL DEFAULT 0,
  `mdate` DATETIME DEFAULT NULL,
  `cdate` DATETIME DEFAULT NULL,
  `deleted` TINYINT(1) DEFAULT NULL,
  PRIMARY KEY (`entity_id`,`phone_id`,`entity_phone_type_id`),
  UNIQUE KEY `id` (`id`),
  CONSTRAINT `FK_cor_entity_phone_1` FOREIGN KEY (`entity_id`) REFERENCES `cor_entity` (`id`),
  CONSTRAINT `FK_cor_entity_phone_2` FOREIGN KEY (`phone_id`) REFERENCES `cor_phone` (`id`),
  CONSTRAINT `FK_cor_entity_phone_type_id` FOREIGN KEY (`entity_phone_type_id`) REFERENCES `cor_entity_phone_type` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Relationship Type Table Set

A Relationship Type Table Set eliminates the practice of having multiple, possibly variable numbered, columns on a table with the same type and similar meanings.The each row in the value table holds what would be in columns and is linked to a type. All Relationship Type Tables must have the following columns:

  • id - Unique integer
  • name - Single word all lowercase name of the relationship type
  • description - Description of the type.
  • mdate - datetime that stands for modified date
  • cdate - datetime that stands for create date
  • deleted - tinyint that specifies whether or not the record is marked as deleted
All Relationship Type Tables are Lookup Tables. The values in this table act as variable column headers.

Example:


CREATE TABLE `cor_entity_phone_type` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` TEXT NOT NULL,
  `description` TEXT,
  `mdate` DATETIME DEFAULT NULL,
  `cdate` DATETIME DEFAULT NULL,
  `deleted` TINYINT(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name` (32))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `cor_entity_phone_type`(`id`,`name`,`description`,`mdate`,`cdate`,`deleted`) VALUES
(0,'default','Default',NOW(),NOW(),0),
(1,'home','Home',NOW(),NOW(),0),
(2,'business','Business',NOW(),NOW(),0),
(3,'mobile','Mobile',NOW(),NOW(),0),
(4,'fax','Fax',NOW(),NOW(),0);

Data Extension Table

A data extension table is a table that extends the data contained within another table. The data within this table has no validity without the data of its parent. This table concept is directly related to the objects and inheritance within the applications. If a child class extends a parent class in the programming object, the child class maps to the Data Extension Table, and the parent class maps to the parent Data Table. This technique allows child tables and parent tables to functionally share the same id. Notice how this table has _id instead of id as its identifier. The purpose of this practice is to allow the inheriting class to instantiate its parent properties without overriding the child's id which may lead to corrupt links if not extremely careful. If a relationship table links to this Data Extension Table, it must use the parenttableidentifier column.All data extension tables must have the following columns:

  • _id - Unique integer
  • parenttableidentifer this is the functional identifier of the parent table, not the id of the parent table. If the parent table is a Data Extension Table itself, it must have a column like parentA_parentB_id.
  • mdate - datetime that stands for modified date
  • cdate - datetime that stands for create date
  • deleted - tinyint that specifies whether or not the record is marked as deleted

Example:


CREATE TABLE `cor_person` (
  `_id` INT(11) NOT NULL AUTO_INCREMENT,
  `entity_id` INT(11) NOT NULL DEFAULT 0,
  `first_name` TEXT,
  `middle_name` TEXT,
  `last_name` TEXT,
  `suffix` TEXT,
  `nickname` TEXT,
  `initials` TEXT,
  `person_type_id` INT(11) DEFAULT 0,
  `mdate` DATETIME DEFAULT NULL,
  `cdate` DATETIME DEFAULT NULL,
  `deleted` TINYINT(1) DEFAULT 0,
  PRIMARY KEY (`entity_id`),
  UNIQUE KEY `_id` (`_id`),
  CONSTRAINT `FK_cor_person_1` FOREIGN KEY (`entity_id`) REFERENCES `cor_entity` (`id`),
  CONSTRAINT `FK_cor_person_type` FOREIGN KEY (`person_type_id`) REFERENCES `cor_person_type` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Type Table

Type tables specify types for a data table. In practice, they are values that might be visible in a drop-down list in an application. The values are relatively static, but might need to change universally.All lookup tables must have the following columns:

  • id - Unique integer
  • name - Value of the type.
  • description - Description of the value.
  • mdate - datetime that stands for modified date
  • cdate - datetime that stands for create date
  • deleted - tinyint that specifies whether or not the record is marked as deleted

Example:


CREATE TABLE `cor_entity_type` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` TEXT,
  `description` TEXT,
  `mdate` DATETIME DEFAULT NULL,
  `cdate` DATETIME DEFAULT NULL,
  `deleted` TINYINT(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name` (32))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `cor_entity_type`(`id`,`name`,`description`,`mdate`,`cdate`,`deleted`) VALUES 
(0,'default','Default',NOW(),NOW(),0), 
(1,'person','Person',NOW(),NOW(),0), 
(2,'company','Company',NOW(),NOW(),0), 
(3,'school','School',NOW(),NOW(),0), 
(4,'schooldistrict','School District',NOW(),NOW(),0);

Lookup Table

Lookup tables hold values that might be common to many objects. In practice, they are values that might be visible in a drop-down list in an application. The values are relatively static, but might need to change universally, so by referencing a Lookup table, this is accomplished easily.All lookup tables must have the following columns:

  • id - Unique integer
  • name - Name of the value.
  • description - Description of the value.
  • mdate - datetime that stands for modified date
  • cdate - datetime that stands for create date
  • deleted - tinyint that specifies whether or not the record is marked as deleted

Example:


CREATE TABLE `cor_userstatus` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` TEXT NOT NULL,
  `description` TEXT,
  `mdate` DATETIME DEFAULT NULL,
  `cdate` DATETIME DEFAULT NULL,
  `deleted` TINYINT(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name` (32))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `cor_userstatus`(`id`,`name`,`description`,`mdate`,`cdate`,`deleted`) VALUES
(0,'default','Default',NOW(),NOW(),0),
(1,'active','active',NOW(),NOW(),0),
(2,'inactive','inactive',NOW(),NOW(),0);
Get Helix PHP Framework at SourceForge.net. Fast, secure and Free Open Source software downloads