core design and user interface

i think we have enough data to create our first module. it will be a simple one that manages the content of the nominal records table. however, remember that it links to 2 other tables via the hierarchy so we now have to allow for them.

More importantly, though, we need to design the css and html that will be used throughout all future modules. Also included in that is the AJAX library and supporting php server files that interact with it.

The difference between calling a function in php and calling it in AJAX is that php can preload the files and then later call the function/class, whereas AJAX calls the function/class by requesting the url. so AJAX requires 2 files where php only requires 1 but this prevents duplication of any code.

reserved nominal accounts

as mentioned earlier, some nominal accounts need to be protected from manual user input, such as the sales and purchase ledgers and the VAT control accounts. Also, any accounts that will be used as bank accounts can be flagged to be reconciled against an actual bank statement. both of these tasks can be achieved by means of the same feature.

it would be tempting to add a column in the core_nom_accounts table to indicate whether the account was restricted or not (or is a bank account). But, that goes against my normalization rules because you only need to reference reserved records, not all of them.

So i am going to create a new table that contains 2 fields, 1 to reference the unique id of the nominal account and 1 to indicate its function.

CREATE TABLE core_nom_reserved(id SMALLINT UNSIGNED, reserveType VARCHAR(2), PRIMARY KEY(id, reserveType))

Now really i should make reserveType a foreign key to another table but i cant be bothered. its a very simple reference that doesn't need much explanation. I will use codes like SL for Sales Ledger, PL for Purchase Ledger, VS for VAT Sales, VP for VAT Purchases, VC for VAT Control and BA for Bank Account (can be more than one).

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