sql defaults

Just a note to say I am editing the tables after they are created. I am using phpmyadmin to do my sql and the defaults are for InnoDB and latin_swedish_ci. So I am changing them to MyISAM and latin_general_ci. You could do this in the CREATE TABLE command but i forgot, and maybe most of you already have these as your defaults anyway so I won't refer to this again but make sure your defaults are as mine.

Actually InnoDB may be a better choice of table for this app, due to the transactional nature of the app, but for now I am sticking with what I know. Any thoughts on the Falcon engine?

chart of accounts

each nominal account has to be categorised in some way so that it can be reported on in a meaningful way. I am going to add a table that summarises a group of nominal accounts. Then, that summary record can be put in the correct place on the chart of accounts: a sort of two-tier hierarchy.

So first lets create the summary table

CREATE TABLE core_nom_summary(id TINYINT UNSIGNED DEFAULT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(30) DEFAULT NULL)

The id from this table can now be used in a new field in the core_nom_accounts table

ALTER TABLE core_nom_accounts ADD COLUMN summary_id TINYINT UNSIGNED DEFAULT NULL

While we are here, I might as well create the table that is the higher of the two tiers. This higher tier will be used to report on as it is for the benefit of external reporting as well as internal reporting.

CREATE TABLE core_nom_chart(id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(30) DEFAULT NULL, sort TINYINT UNSIGNED DEFAULT NULL)

I have just remembered that I need to put a sort column in the other tables

ALTER TABLE core_nom_accounts ADD COLUMN sort SMALLINT UNSIGNED DEFAULT NULL

ALTER TABLE core_nom_summary ADD COLUMN sort TINYINT UNSIGNED DEFAULT NULL

Oh and I need to link the summary table to the chart table

ALTER TABLE core_nom_summary ADD COLUMN chart_id TINYINT UNSIGNED DEFAULT NULL AFTER title

Ok I think thats it for now.

nominal database structure

well, all this blogging is giving me an itchy coding finger so i am going to start with some core sql setup commands. The easiest part to start with is the nominal account table as it contains just the header files (assuming i keep it strictly normalised).

I am assuming you have a database ready and are logged in as a user of it. For now the coalition I will use is iso-8859-1 (latin1).

CREATE TABLE core_nom_accounts(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(30) DEFAULT NULL)

There, that was fairly painless. The table structure isn't finished but it will do for now.

dual base currency

i am still unsure about this. i do have a plan on how to structure the database, should i go ahead with dual base, but i still think the target market isn't interested in this feature.

in fact, I will make an executive decision now not to include it unless demand from  the target market picks up.

application structure: classes

I thought i'd take a break from nominals and the like as I have been thinking about how to make this an app that developers can contribute to. Also, I have been thinking about how to create the different sections (sales, purchases, etc.) and how to integrate them together so that they can share common data and functions easily.

Then it hit me: class extension. Its still a new concept to me but, (and correct me if i'm wrong,) you can have a base class that others "extend", or, copy and add to. So if I provide a core class that contains all the necessary functions needed to make the app work, then me, or third party developers, can simply "drop in" a class that extends the core but is there to do a specific task. App owners can then simply choose which modules to activate on their app. I know this all sounds a bit vague at the moment but i think once i explain it in more detail you will understand.

OK, first I checked for php functions related to classes and I found one called get_declared_classes(). Now, lets say "drop in" modules are put into a folder that is read on application load (each module should be a class definition and so will appear in the list that get_declared_classes() returns), then those modules can be included everywhere in the app - the navigation menu, the admin panel etc. The problem with this method is that by default, the get_declared_classes() function will return over 100 already declared classes (on my server anyway) so a naming convention would have to be enforced for modules to work. hmmm.

Ah! I may have found a solution: php has a function called is_subclass_of which, as it suggests, checks if a declared class extends a specific one already declared. So if the app loads the classes in the module folder then checks for all classes that extend the core, they can be used in the app. The only problem now is name clashes - but after so much brain storming i think i will leave that problem until later

reserved nominal accounts

I said earlier that VAT has its own nominal account (actually it uses a few) which suggests that posting transactions to it could cause a problem at audit time. Normal practice is to reserve certain accounts in such a way that they can only be posted to through approved procedures. Therefore, it is necessary to mark these accounts so that stray transactions cannot be posted to them.

These accounts include Bank Accounts, VAT Accounts, Sales Ledger, Purchase Ledger and possibly more.

nominal account structure

there are various ways to design a nominal account structure. 

For example, applications like Sage recommend a number (between 1000 and 9999) for each nominal account such as 1000 or 4100. Numbers between 1000 and 1999 belong to 1 section of accounts (usually assets) whereas numbers between 4000 and 4999 belong to another (usually sales). This helps when visually looking at accounts as to where they belong. Then a separate chart of accounts is used to display summary information about those nominal accounts. Sage allows multiple charts to be used depending on what information is required.

Other applications use a simple unique auto_increment key for each new account and set where it belongs in the chart of accounts within the nominal account directly. this means only 1 chart of accounts can be used but makes the db structure far simpler.

i speak of a "chart of accounts" as if it is necessary to have but in reality it isn't. What is necessary includes a Trial Balance, a Balance Sheet and a Profit & Loss Account. Extra reports could be made available on demand but would more likely be for internal purposes. (oops! nearly forgot about VAT reporting).

Nominal Transactions

In an earlier post, I showed you how a dummy transaction would affect the balance of 2 Nominal Accounts and thus the totals of the Debit and Credit columns while still maintaining a balanced Nominal Ledger.

So can you now envisage how the structure for a nominal transaction may appear?

You may think to have 1 value with 2 nominal accounts so that 1 account is "Debited" with the value and the other is "Credited". That would work with our last example but check this one out. Would it work here?

You are VAT registered. The Widget sells for £10 + VAT = £11.75 (£1.75 VAT). Now we would have a "Sales Widgets" Account balance of £10 Credit, a Sales VAT Account balance of £1.75 Credit and a Bank balance of £11.75 Debit.

You now have more than 1 value to deal with and more than 2 Nominal Accounts. Some applications have dealt with this by adding another column to the transaction specifically for VAT purposes. It works, but not all transactions have a VAT element to them (cash to bank etc.). A nominal ledger will have a dedicated Nominal Account for Sales VAT so why not just treat the VAT as another part of the transaction. In other words, each transaction line consists of a date, a value, and 2 Nominal Accounts, Where more than 2 accounts are needed, the transaction simply has more lines added to deal with them.

For Example

Date    | Value | D A/c | C A/c | Notes

===================================================

1-1-09  | 10.00 | 9999  | 4000  | Sales A/c [Cr]

1-1-09  |  1.75 | 9999  | 4999  | Sales VAT [Cr]

1-1-09  | 11.75 | 1000  | 9999  | Bank Account [Db]

===================================================

Ok, so now we have dealt with all 3 Nominal Accounts by giving each its own line and using a holding dummy code (9999) to check the arithmetic before the transaction is posted (it has to balance before it can be posted in order to keep the Ledger balanced).  The [Cr] accounts add up to 11.75 and the [Db] accounts add up to 11.75 so the transaction balances! Got it?

NB: Ideally the lines belonging to each transaction would be identifiable by a transaction id on each line. Then common data such as the date can be held in a header row just once rather than on each line (normalisation kicking in :) )

Nominal Accounts

Earlier we learned that the Nominal Ledger should always balance and that this is checked by adding up the Debit and Credit columns of it. These columns are made up of individual entries called Nominal Accounts. Each Nominal Account holds a balance value for a particular financial aspect of the business, such as sales, carriage, rent, bank balance and so on.

Most Nominal Accounts are designated to either the Debit or Credit column in advance, regardless of the balance (balances can be positive or negative). For example, the Bank Balance Nominal Account usually appears in the Debit (Left) column where as a sales account, say "Widget Sales" would appear in the Credit (Right) column.

Now, before any transactions take place in the business, it follows that all Nominal Accounts would have a balance of zero regardless of whether they appeared in the Debit or Credit Column. Therefore, adding up both columns would yield zero for both so, it balances with a zero on both sides.

Now if we sold a widget for £10 and put the money straight into the bank (via a PDQ) then the Credit value of the "Sales Widgets" Account would increase by £10 because we have sold £10 worth. Also, the Debit value of the "Bank Account" Nominal would increase by £10 because we now have £10 in the bank. Add up both sides and we get £10 in each - it balances!

This is, in essence, how the Nominal Accounts keep the whole Nominal Ledger balanced. With this in mind, you should now start to think of how you would structure the db table to accommodate the Nominal Accounts.

Application tasks - the Nominal Ledger

Now lets look more at what we want our application to do. At the core of any accounting or book-keeping application is the nominal ledger. "The what now?" Exactly, its the bit of the application that should never be exposed to non-financial personnel, yet some software companies insist on using "Nominal" in the navigation of their application. Why? I have no idea but it won't happen here on my watch. 

Unfortunately for us developers, we have to deal with it along with all the other ledgers we will meet but don't bottle out yet - its actually a lot simpler concept than you might expect.

If we don't expose the nominal ledger to the user, then what is its purpose? It tracks all financial transactions made in the application in such a way as to make it easy to retrieve later on, either for legislative or managerial reasons. By financial transaction we mean the movement of values across accounts not just actual cash and cheque transactions. Agh! I hate trying to explain what a nominal ledger is so, for now, imagine its like having lots of bank accounts that you need to keep track of (it isn't I know but its a start).

the key to the Nominal Ledger is that it should always balance. Huh? Well imagine a pair of scales that weighs food, you know the old style ones with a tray hanging on either side. In order to get the scales to balance, you need exactly the same weight on each side. Similarly, the nominal ledger has a "Left" and a "Right". And in all cases the sum of the Left column should always, ALWAYS equal the sum of the Right column. In that way it is said to balance.

Strictly speaking, the Left is actually called the Debit and the Right is called the Credit. When we alter the values in the nominal ledger, we "Post" a transaction to it. That can be done via another transaction, like an invoice, or directly, via a journal entry. As a developer, just remember that both sides have to balance before and after each transaction.

Objectivity

The final factor is Objectivity. This is nothing to do with class programming but rather what we want the Application to achieve. Sounds simple enough but its all too easy to be all things to everyone and actually lose focus in the process of doing so.

We are targeting small businesses and, as such, we have to develop this application with small business owners in mind. In most cases, they would gladly trade away glossy features in exchange for doing as little entry as possible.

In any case, you need to think like a small business owner to understand what they want, and don't want, from a book-keeping application. Owners are not bothered about nominal accounts or quick ratios but are keenly interested in ow much money they are owed and whether there is an easier way to get it.

Application Structure

Yet another factor that I am not completely decided on is application structure. For example, should the data for ALL clients be kept in 1 db or a separate db for each? If kept in 1 db, sould the data be amalgamated in 1 set of tables, or should there be a set of tables for each client?

These are questions to which I don't think have "right" or "wrong" answers, but, just different consequences. Separate db's allows far greater room for individual clients to tailor their application but poses a problem for universal authentication.

Structure also covers things like OOP (use of classes), use of AJAX, ways to authenticate and so on. We will get to these areas later on but, for now, just try to think of all areas within Application Structure.

Again I think more input is needed here before a final decision can be made.

-- Just a thought that is the db's were separate, should a client want bespoke development on their data, it would be far easier to do, even if they contracted their own developer to do it.

Modularity

A related factor to accessibility is modularity, or the ability to implement new aspects to the application without having to rewrite the existing parts.

this suggests a central log that added modules can interrogate to see whether the entries are relevant to them. The added modules should make themselves aware to the rest of the application so that they can be accessed via menu options and proper authorisation can occur.

I think this needs to be addressed further so comments here are especially welcome.

Scaleability

Another factor to write down is Scaleability, or the ability to allow the business to grow without having to change how to account for it financially.

True, this application is aimed at small businesses, but even small businesses grow, and it would be a shame not to let them take their book-keeping software with them just because it can't keep up. Different businesses grow in different ways; some feel the need to use an asset register; some may want to account for stock movements more accurately; some may implement ISO9000 and all that that brings in its wake. Most of these features probably won't be in the initial launch but thats not to say they can't ever be added. Scaleability will determine just how and what can be added as time progresses and businesses grow.

Accessibility

Yup, accessibility is your second factor and is actually one of the driving forces that led me to the whole SmallAccounts idea.

Lets just qualify what accessibility means in this case. Its the understanding of who is allowed to do what on the application, and from where. Under technical terms, "who" and "what" are dealt with later under the section of authentication and authorisation, but the key word here is "where". A normal low-priced book-keeping application would normally only be accessed from one computer. Network versions may be available but normally incur a charge, and most of them are only available to other computers on the local network (and of the same computer type).

But as our application is web-based, the only restriction is whether someone has access to the internet or not. No special software is needed (assuming you don't go down the route of browser specific controls like ActiveX) which means that any computer type can access the application just by being able to access the internet. I am writing this blog on a Mac but, statistically speaking, you will probably be reading it on a Windows PC. It doesn't matter.

Now imagine the business owner above dumping his paperwork on Mrs. Accountants desk. Mr Accountant hasn't got time to input the invoices so he delegates that task to Miss Book-Keeper, who accesses SmallAccounts on her cheap PC and ploughs through the paperwork for the rest of the day. Thats just one scenario but its one i have seen in action. Ok so any desktop application on Miss Book-Keeper's PC would have done the same, but, suppose Mr Owner wanted to "enter" the deposits he has received and paid into the bank, just to save a bit of expensive time from his accountant. Could Miss Book-Keeper's application allow that? Would she allow it? It would likely be out-of-hours when Mr Owner could find the time to do it so he would probably have to do it on his home PC. 

Get the picture?

Government Legislation

Now we have already mentioned a factor to add to our application, that of Government Legislation so write that as your first factor. I have lost touch with red tape so I would appreciate anyone's views on the current requirement for book-keeping software. 

I do know the VAT office had certain requirements although it has now merged with the Revenue office (HM Revenues & Customs) which may or may not make compliance easier. So this is a Factor simply because they have expectations of any software that claims to keep books.

Its good to note at this point that SmallAccounts is targeted at very small businesses (like one-man-bands etc.) that may or may not be Limited and may or may not be VAT registered. You know the kind of proprietor who stashes all his/her paperwork into a carrier bag and dumps it on the accountants desk every April 5th. So now you may be thinking "How does SmallAccounts benefit a business owner who doesn't actually want to do, and doesn't do, his/her own paperwork?" Well, for now, lets just say that another factor to add to our list is Accessibility. 

start at the beginning - which is where?

Think about it. Where would you start? Its so easy to just dive in to your favourite IDE and develop code, or pull up MySql Query and create tables. Thats what I did when I first got the idea. 9 years later I still don't have an application. Why? Because I developed in a very narrow-minded way and by the time I realized how far up a dead-end I had gone, I lost the will to retrace my steps, so I shelved it all.

I am not going to make that mistake again. i know I can't account for everything that might change (especially government legislation) but you need to take plenty of steps back on this one. In fact, put away you IDE's and get out a piece of paper and a pencil. You probably won't need your IDE for some time. Believe me, its the best way to start something like this.

So, you have your pencil and paper? Good. Start by writing the word "Factors" at the top - underline it or embolden it in some way because this piece of paper will be your most important during the development process. It will form the blueprint of what will become, essentially, the business plan of your code. Look out for the FACTOR label in the blog 

System Requirements

I am developing on a linux system x64 with a standard LAMP setup. MySql version is 5.0.27, PHP version is 5.1.6, Apache version is 2.2.6. I am also using ProtoType 1.6.0.1 for JavaScript and AJAX calls.

I am trying to use as few "wrappers" as possible but I found ProtoType to be a fantastic achievement and a doddle to use so it gets my thumbs up. Solely for future-proofing I am going to use the mysqli interface between PHP and MySql (comments please). Also to hand is a simple, yet great, function called xmlize. It converts a piece of XML into an array which becomes so much easier to work with when you don't know XPath and XPointer.

I think thats it for now: if i discover anything else thats been added later I will let you know.

blog notation

Ok so I will split the blog into the various technical elements that will make up the application. The obvious ones are SQL, PHP, CSS and JS. I may introduce XML later on especially as I plan to integrate SmallAccounts into existing web shopping carts in the future.

So when browsing this blog, if you want to see all SQL statements, for example, click the label called SQL. I may include more than 1 label in any given post depending on its relevance but it will be obvious to you if I do.

I will format code in a monospace font for easier reading and copying (yes I encourage you to rip my code, I will even provide a regularly updated download for you as we progress).

audience for this blog

If you are looking to help a project get off the ground by contributing to it, or you want to copy what I am doing to improve your skills, then I suppose this blog is for you. Let me stress here that I do not claim to know the best way to do this, I just know what I want to achieve at the end.

For example, I know how to OOP program in php but I don't fully understand how to scheme out classes in the best manner: extends? abstract classes? static calls? Its all new to me so I can only go with what I know. So this is where your comments come in handy - especially if you know better than me :P

history of SmallAccounts

Once upon a time I trained as an accountant. But i got bored after reaching my AAT technician level and, to cut a long story short, became a web developer which is what I do for a living now.

But I never really left accounting, I just got bored of it. But what if I could combine my knowledge of accountancy, my experience as a book-keeper and my skills as a developer to create a unique piece of software that acts as a book-keeper?

Many years later I still haven't really started to put anything into code or even onto paper: its still all in my head. But I have been using blogger for some time now and so I will now lay the framework for my application I have named SmallAccounts.