Data Model

This section describes the relational data model of the shared tables used in Open Accounting. Understanding this model can be an aid in the customization, installation, and maintenance of the system.

Story Description

Open Accounting allows billing of job time and expenses to various Accounts. Each account is uniquely identified by two data items: a link to Organizations and a link to Contacts. So the system can bill for more then one company (although only one is active at any given time) and one person can be a customer of multiple companies.

Organizations contain identifying information about the billing entity: company name, slogan, street address, and so on. Each organization has a unique identifier of one to four characters. This means abbreviations, such as LBG, can be used as identifiers.

Contacts also contain identifying information. There is at least one person assigned to a contact, and there may be more then one. Contacts have Priorities and Roles. Assignment of names to these is arbitrary, however the application enforces relational integrity to the Priorities and Roles tables.

There are two types of Transactions: billings and payments, or more precisely billings and not billings. Transactions are always made against an account. There is no attempt to normalize Transactions: each record has space for all of the information necessary to be either a billing or a payment.

Transactions reference Categories and Types. A category is the reason for a transaction: a category helps us understand why something is billed or paid. Sample Categories include consulting, service calls, webmastering, retainers, general expenses, hardware, and software.

Types apply only payments. In fact the type “Billing” is reserved for, surprise, Transactions that are in fact billings. No payment (non-billing transaction) may have the billing type, which is a “magic number” as explained below. Types are payment vehicles like cash, checks, and credits applied to the account.

Magic Numbers

There are several “magic numbers” in the system: the billing type and the boundary between goods and services. There is a third magic number, the boundary between payments and credits, but it turns out to be the same number as the other boundary. There is one system reserved magic number. And even though it is a string, the word "Reconcile" is a reserved deposit number.

Billing Type of 100

As mentioned earlier, all Transactions that are billings have the same type. This is the integer value 100. Application code depends on this type being 100, but it can be named something besides Billing.

Payment Category of 100

Transactions that are payments may have the same type-or not, depending on the configuration. If detailed payment processing is on, payments have the same category as the corresponding transaction. If this feature is off, all payments have a category of 100. See the paragraph on Detailed Payment Processing in the Operation section for more details.

Boundary of 10,000

There are two basic Categories: expenses and service. Expenses are reimbursements, but services represent gross revenue. The boundary between both is 10,000, with Categories less then 10,000 considered expenses and 10,000 or more considered services. Note that one service exactly equals 10,000: Retainer.

Something else about Categories: detailed payment processing is applied in priority order based on the numerical value of the category’s ID, or primary database key. Lower numbers receive consideration first. So the list as shipped starts with penalty fees, such as late fees, followed by expenses, followed by services.

A similar scheme is used to classify Types: values less then 10,000 represent payments received from customers: cash, checks, and so on. Values 10,000 and over are considered credits applied by management in lieu of payments to balance an account. Examples include warranty service credits. Unlike Categories, the boundary value of 10,000 does not represent anything special.

System Reserved Undefined Value

The “magic number” zero represents an undefined, system reserved value. For example no transaction should have a category or type of zero. The system checkes for improper use of zero in some (but not all) cases. .

System Reserved Deposit Number of “Reconcile”

When posting payments, one can choose to record a deposit number immediately or at a later time, perhaps after the deposit has been made at the bank. The system uses the deposit number “Reconcile” for post-deposit reconciliation. Do not use a deposit number of “Reconcile” for any other purpose.