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.