Proposal for 1.4: CoA structure redesign

["Chris Travers" <..hidden..>]

Hi all;

I am proposing the following structure for the data storage of CoA
information in 1.4.  The advantages would include:
1)  Hierarchical headings would allow for nested summary totals
2)  Fully normalized structure (as far as I can tell).

Here is the structure I am looking at:

CREATE TABLE account_type (
    id serial not null unique,
    label text primary key
);

account type includes asset, income, expense, liability, and equity.

CREATE TABLE account_heading (
    id serial not null unique,
    label text, -- label is similar to accno at the moment-- is the primary key
    description text not null,
    parent int references account_heading(id),
    primary key(label)
);

CREATE TABLE account (
    heading int not null references account_heading(id),
    label text, -- label is primary key and is used as the account number
    description text not null,
    account_type int not null references account_type(id),
    is_contra bool not null default false
);

CREATE TABLE account_use_category (
   id serial not null unique,
   label text primary key
);

CREATE TABLE account_use_type (
   id serial not null unique,
   label text,
   category int references account_use_category(id),
   primary key (label, category)
);

CREATE TABLE account_use (
   id SERIAL NOT NULL UNIQUE,
   account_id INT REFERENCES account(id),
   use_type int REFERENCES account_use_type(id),
   primary key (account_id, use_type)
);

The tax tables would probably be handled a little differently as well
but I am still working that one out.

any feedback or thoughts?
Best Wishes,
Chris Travers