PBooks Data Model

This information can also be viewed in the wiki:

http://www.pbooks.org/resources/mediawiki/index.php/PBooksDataModel

This is the lowest common denominator db model for an accounting system. As the foundation for the accounting system, the database has to be uber-simple, and not full of cruft.

It is now ACID compliant using the InnoDB storage engine:

-- -- Table structure for table `pb_accounts`-- 

CREATE TABLE `pb_accounts` (  `id` int(11) NOT NULL auto_increment,  `name` text,  `account_type_id` int(11) NOT NULL default '0',  `description` text,  `creation_datetime` timestamp NOT NULL default CURRENT_TIMESTAMP,  `account_number` int(11) default NULL,  PRIMARY KEY  (`id`),  UNIQUE KEY `account_number` (`account_number`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1043 ;-- ---------------------------------------------------------- -- Table structure for table `pb_entries`-- 

CREATE TABLE `pb_entries` (  `entry_id` int(11) NOT NULL auto_increment,  `entry_datetime` timestamp NOT NULL default CURRENT_TIMESTAMP,  `memorandum` text,  `entry_type` enum('adjusting','budget','comparative','external-accountant','standard','passed-adjusting','eliminating','proposed','recurring','reclassifying','simulated','tax','other') NOT NULL default 'standard',  PRIMARY KEY  (`entry_id`),  KEY `entry_datetime` (`entry_datetime`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=510 ;-- ---------------------------------------------------------- -- Table structure for table `pb_entry_amounts`-- 

CREATE TABLE `pb_entry_amounts` (  `entry_id` int(11) NOT NULL default '0',  `entry_type_id` enum('Credit','Debit') default NULL,  `entry_amount` decimal(10,2) default NULL,  `account_id` int(11) default NULL,  `memorandum` varchar(100) NOT NULL,  `posting_date` date default NULL,  `posting_status` tinyint(2) default NULL,  KEY `entry_index` (`entry_id`),  KEY `account_id` (`account_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- ---------------------------------------------------------- -- Table structure for table `pb_entry_metadata`-- 

CREATE TABLE `pb_entry_metadata` (  `meta_id` bigint(20) NOT NULL auto_increment,  `entry_id` int(11) NOT NULL,  `meta_key` varchar(255) NOT NULL,  `meta_value` varchar(255) NOT NULL,  PRIMARY KEY  (`meta_id`),  KEY `entry_id` (`entry_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;-- ---------------------------------------------------------- -- Table structure for table `pb_general_ledger`-- 

CREATE TABLE `pb_general_ledger` (  `transaction_id` int(11) NOT NULL auto_increment,  `entry_datetime` timestamp NOT NULL default CURRENT_TIMESTAMP,  `memorandum` text,  `entry_amount` decimal(10,2) default NULL,  `balance` decimal(10,2) default NULL,  `account_id` int(11) NOT NULL default '1002',  `entry_id` int(11) NOT NULL default '0',  PRIMARY KEY  (`transaction_id`),  KEY `account_id` (`account_id`),  KEY `pb_general_ledger_ibfk_2` (`entry_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=919 ;-- ---------------------------------------------------------- -- Table structure for table `pb_options`-- 

CREATE TABLE `pb_options` (  `option_id` smallint(11) NOT NULL auto_increment,  `option_key` varchar(255) NOT NULL default 'untitled',  `option_value` varchar(255) default NULL,  PRIMARY KEY  (`option_id`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;-- -- Constraints for dumped tables-- -- -- Constraints for table `pb_entry_amounts`-- 

ALTER TABLE `pb_entry_amounts`  ADD CONSTRAINT `pb_entry_amounts_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `pb_accounts` (`id`),  ADD CONSTRAINT `pb_entry_amounts_ibfk_2` FOREIGN KEY (`entry_id`) REFERENCES `pb_entries` (`entry_id`) ON DELETE CASCADE;-- -- Constraints for table `pb_entry_metadata`-- 

ALTER TABLE `pb_entry_metadata`  ADD CONSTRAINT `pb_entry_metadata_ibfk_1` FOREIGN KEY (`entry_id`) REFERENCES `pb_entries` (`entry_id`) ON DELETE CASCADE ON UPDATE NO ACTION;-- -- Constraints for table `pb_general_ledger`-- 

ALTER TABLE `pb_general_ledger`  ADD CONSTRAINT `pb_general_ledger_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `pb_accounts` (`id`),  ADD CONSTRAINT `pb_general_ledger_ibfk_2` FOREIGN KEY (`entry_id`) REFERENCES `pb_entries` (`entry_id`) ON DELETE CASCADE;

Data Model Notes:

• Accounts have descriptions, whereas journal entries, entry amounts, and general ledger posts all have memoranda.
• General ledger posts are currently dependent upon journal entries, however I plan to make them dependent upon entry amounts, so that if a journal entry amount is changed, the journal post is deleted and will need to be re-posted.

Data Model To-Do:

• Make a unique key on pbentryamounts based on entryid and accountid. As far as I can tell, there is no purpose to having more than one entry amount in a single journal entry for the same account. To do so, there will need to be two journal entry placeholders - one for credit and one for debit. This is problematic when it comes to compound journal entries. Explore "ON UPDATE CASCADE" as option.
• Consider linking pbentryamounts.memorandum with pbgeneralledger.memorandum