Archive for the 'PBooks Data Model' Category

Major SQLite Progress

Published
by
Albert
on March 11, 2008
in PBooks Data Model, Pbooks Development and SQLite
. 0 Comments

I’m very pleased to report that there has been some seriously major progress towards running PBooks with SQlite. The major hurdle was the way that I had structured SQL statements with “LEFT JOIN”. Once I found a better way to draft them, specifically using “INNER JOIN”, the incompatibility went away.

Besides the JOIN issue, I also had to remove the use of SUBSTRING and DATE MySQL functions. Unfortunately those are not the same in SQlite. Instead I’ll write an action to use PHP’s substring, as I’m sure that will still be faster than XSL’s substring capabilities.

Also note that there are two new SQL files - one for the structure and one for some sample data. Using SQLite with PBooks is still very far from being complete, but its a lot closer than it was a few hours ago! :-)

For more information, see PBooks and SQLite

Connecting the Dots

Published
by
Albert
on February 12, 2008
in PBooks, PBooks Data Model and Pbooks Development
. 0 Comments

I have some questions about the income statement report.

First of all, how do the cash flow transactions (i.e. deposit or withdrawal to a cash / bank account) get connected back to their corresponding journal entries? For example, when a bill is received, it may get entered into the accounts payable account. Later on, a check gets issued, and accounts payable is credited.

The report query will find outgoing transactions, but in this example there is not corresponding expense account. The credit to the accounts payable must somehow be linked with the “journal of origin” to find out which expense it should be matched with.

While this seems a bit obscure, its actually a very important factor to retain in PBooks. At the moment, I’m thinking that there should be a method for linking several journal entries together. Because this is a fairly simple relationship, it might make sense to have a simple, two-column table that can string journal entries together.

Dogfooding




Published
by
Albert
on December 20, 2007
in Accounting Software, Bookkeeping Notes, Invoices, PBooks, PBooks Data Model, PBooks Testing and Pbooks Development
. 0 Comments

If you are unfamiliar with the concept of “dogfooding”, see here.

It makes a big difference in the start of a project to have a ready use case, and thanks to my own personal use of PBooks, I just added a terrific new feature. Well, I think its terrific. :-)

The feature is similar to the matching process, which is used to match transactions to additional accounts. For example, if you have a CSV file containing credit card transactions, in bookkeeping terms, those only represent half the journal entry. You can import those transactions into the ledger, and the matching page will show ledger transactions without a corresponding entry.

On the flip side, the posting page will display only the journal entries which need to have entries posted to the ledger.

If your more of a business process user as opposed to a accounting / bookkeeping user, this might not sound too exciting, but I think its pretty cool. I was able to implement this feature fairly easily thanks to the relatively new journal entry status field, which is a tiny int so it shouldn’t have too much of an increase on the database size.

Also, in my personal use of pbooks, I’ve ironed out several bugs which were hiding from general view. Since most of the bookkeeping work I’m doing is prior, I haven’t worked too much with the new business process models, but I will next week when my company sends out invoices, as well as whenever we receive a payment and / or make a deposit.

PBooks 0.05 Progress

Published
by
Albert
on December 9, 2007
in PBooks, PBooks Data Model and Pbooks Development
. 0 Comments

Interest and attention always helps! After getting an email from someone new about PBooks, as well as some continued interest from a couple of people, I’ve got some inspiration to work away on PBooks tonight (Sunday at 8PM, of course!).

I fixed and filled-in a bunch of minor invoice bugs and omissions, so they are really starting to work well now. The model for invoices is still very loose, but as long as the use cases are functionally successful, the data model can be tightened up later.

After working with invoices for awhile, I was glad to see how the entry -> business transaction abstraction was performing. In many ways, the mental model for a business is much more complicated than the underlying accounting and bookkeeping concepts, and bridging the gap between debits and credits and invoices and checks can be a little tricky, but by keeping it simple (and maybe a little bit too loose at the moment) I think PBooks will make it out OK.

In other words, businesses today can still manage their finances with a simple journal and ledger, but that takes time, and to save time, that process can be integrated with their existing business processes, and that’s what we’re trying to accomplish with PBooks. So to reiterate what I’ve already said a zillion times, I’m continuing to focus on the accounting and bookkeeping stuff first before rolling it out to the business processes for integration. That evolution is starting now, and is casting a very wide net, requiring some seriously loose coupling. In good time, we’ll tighten it up.

I’m hoping to get version 0.05 out by the end of 2007, and get the demonstration updated to that version as well. Thanks for your continued interest in PBooks!

Metadata Revisited




Published
by
Albert
on October 27, 2007
in PBooks, PBooks Data Model and Pbooks Development
. 0 Comments

I’m still circling around this idea of metadata and I need to do more research on it. Is there a PHP library or something which can provide a persistent layer of an XML document object? I think its possible to use something like memcached to serialize php data objects, but I’m not sure if that’s the right solution here.

XSL to the rescue?

Maybe I should create some XSL templates which could simply transform metadata into a more cohesive object on a regular basis. That would simplify the webGUI templates to deal with HTML components, and separate the key-value pair object relationship mapping to the metadata templates. Yes I like that idea better.

To be more specific, metadata models are currently described in XML files. It doesn’t make sense to use SQL to map key-value pairs, its too complicated. So I’ll setup one SQL statement to fetch the data, another to fetch the metadata, then use an XSL transformation to “zip” the two together and build a cohesive object for the user to manipulate. Then hopefully it will be just as simple to unzip the data and the metadata in a standard manner to prepare a SQL statement to store the data, or even transition it to another state.

The one problem with this idea so far is that I’m not sure how to get nexista to accomplish this, I’ve really only used xsl within nexista to output over http. Actually I could do something similar to how the account groups are handled (using curl to fetch an XML document internally over http), but in that case I’ve only worked the output or zipping together of the data, not unzipping it for inserting into a database. Hmmm.

Complexity Creep Alert!

UPDATE October 29, 2007: I’ve done some work on this today and I’m running into the fact that invoices are regular entries with extra metadata and getting confused. I’m using xslt to build a new invoice-specific element, using the metadata, and finding that I might be simply making things a lot more complicated for myself. I’m good at that! :-)

Anyway, I’m not sure if I should add the entries and entry_amount fields to the invoice metadata definition, or if I should use XSL to merge the two together. On the one hand, I don’t want to recreate what’s already been done in the entry model, but I also don’t want to have a super complicated XSL file. Know what I mean? More to come soon….

Compromise

Well for now I’m just going to design for practical purpose, rather than eternal bliss. I’m going to add one custom crafted node to the invoice xml output, the invoice amount. This will be lifted from the entry amounts table, as the amount entered into the accounts receivable account.

The Authority

By the way, I did a quick search on XBRL invoices and came up with this.