PBooks Data Model
From PBooks Open Source Accounting Wiki
Contents |
[edit] The Conceptual PBooks Data Model
There are seven core tables in the PBooks data model. While a normal PBooks installation will usually have more than this (for user management and CRM purposes, for example), I'm trying my best to keep the core model clean of cruft and scope creep.
The core tables are:
- accounts
- account_metadata
- entries
- entry_amounts
- entry_metadata
- general_ledger
- options
The metadata tables contain information about the data stored in the other tables. For example, it might make sense to have a "visible" column in the accounts table, but that is not information which defines an account, it is more like information which is about an account, so it will go into the account_metadata table. The account_metadata table will also contain information like whether the account is a checking account, has a specific type of depreciation, or really any type of information which isn't relevant to each and every account there is.
[edit] Account Groups
Account groups have split up and complicated the accounting database a bit. I'd like to combine the pb_account_group_parents and pb_account_parent_groups tables, with an enum flag dictating the type.
[edit] Account Types
When I started PBooks, I used 5 account types:
- assets
- liabilities
- equity
- revenues
- expenses
And to better align with the accounting world at large, I added a few to match with the XBRL specifications. PBooks now has the following account types:
- assets
- liabilities
- equity
- revenues
- expenses
- gain
- loss
- contribution to equity
- distribution from equity
- comprehensive income
- other
Because this information is most likely to be static, it is not stored in the SQL database. But that doesn't mean it can't be changes, it is stored in an XML file. See PBooks XML Account Types File. 'Even if the types themselves aren't changed, it might be helpful for user's to change the account_type_id's. CAUTION: the system is not setup for changing these identifier keys in a live database; doing so will lead to unpredictable results, and data corruption is likely.
[edit] Account Numbers
Due to the variety of numbering schemes which are employed in accounting firms and bookkeeping services, the ability to change account numbers on demand is supported. Each account uses a unique key as its identifier within the database, which is separate from its account number. Thus, even if the account number is changed, the relationships between transactions and their accounts are maintained.
[edit] Invoice Data Model
The PBooks data model extends the journal entry model. An invoice is a journal entry with one side of the entry being accounts receivable, and the other being one or more revenue sources. What about additional information, like which client the invoice is for, the invoice age, and other important stuff? That is stored in the entry metadata table.
I'm sure some will balk at the idea of storing this information in a journal entry metadata table, and I'll just say that I'm not 100% convinced that its the right way to go. Depending on how it works out I may switch it up at some point in the future.
[edit] Journal Entry Status Codes
- 2 = default, status unknown
- 9 = temporary, will be deleted if not updated
- 20 = entered, not yet posted, unlocked, can be edited
- 21 = fully posted
- 30 = locked
- 40 = previous accouting period
- 50 = audited / confirmed
- up to 99
The bold entries are the ones that are currently used.
[edit] General Ledger
The general ledger is the individual transactions of each account, and as of January 2008, I'm thinking about adding the ability to label transactions. The ability to label transactions will initially be used to group transactions into statement groups. For example, a credit card account may have hundreds of transactions, and it might be convenient to view, sort, and browse them as they appear in the credit card issuers statements.
General Ledger Transaction Labels
[edit] Database Performance Ideas
Currently, database performance is fine, but for higher transaction frequency and large time spans, database performance will grow increasingly important. Here are a couple of ideas for improving database performance:
- memcached
- Better indexing
- quality SQL
[edit] Non-Accounting Data
Basic bookkeeping practices require ancillary data like inventory, customers, and employees. PBooks plans to provide minimal support for this type of data but will strongly refrain from trying to be everything to everyone. The minimal support will include table fields for foreign keys, for example a journal entry that represents an invoice will have a metadata key and value for the customer_id. The customer id will refer to an external database not directly managed by PBooks.
From the user interface standpoint, the application will have several hooks to tie data sources for this non-accounting information to be integrated into PBooks, and PBooks will include sample data sets to act as placeholders. As of December 2007, PBooks includes a sample XML file containing some basic example customer records, and we'll soon add more for inventory, vendors and employee records.
TODO: there is information regarding customers which should be included in the PBooks database - credit limit, taxes, and special pricing information - this would presumably get stored in account metadata where the customer would be represented as an account? Does it make sense to take a feed of customer and create accounts for them?
For more information on how PBooks will integrate this information, see the PBooks API.
[edit] The PBooks Data Model in SQL Format
[edit] MySQL
For formatting purposes I've moved the SQL to a new page: SQL Dump of PBooks Database
[edit] Postgres
I haven't tried Postgres yet but I think it should work fine.
[edit] SQLite
Use of SQLite with PBooks is possible but has not been extensively tested and is considered experimental. See PBooks and SQLite for more information.
[edit] Financial Modeling Resources
- XBRL
- GnuCash Homepage and a copy of the GnuCash SQL model
