Proposed schema for payroll

[Chris Travers <..hidden..>]

Hi;

Based on a number of discussions I had yesterday, I came up with the
following schema for a payroll module.  I wanted to submit here for
further feedback.

CREATE TABLE country_income_class (
    id serial not null unique,
    country_id int not null references country(id),
    name text,
    PRIMARY KEY (country_id, name)
);

COMMENT ON  TABLE country_income_class IS
$$ This stores basic income classes for payroll tax purposes.  Note
that different income types may be taxed differently.
This table should not be maintained by the company but by national
payroll modules.
$$;

COMMENT ON COLUMN country_income_class.name IS
$$ This column must be standardized by a country payroll module $$;

CREATE TABLE income_type (
    id serial not null unique,
    income_class_id int not null references country_income_class,
    base_unit text,
    account_id int not null references account(id),
    label text not null unique,
    primary key (income_class_id int, base_unit text, account_id)
);

COMMENT ON TABLE income_type IS
$$ This table stores the income types used for payroll.  These are set
up per company.$$;

CREATE TABLE payroll_functions (
    id serial not null unique,
    country_id int not null references country_id,
    sproc text primary key,
    label text not null unique
);

COMMENT ON TABLE payroll_functions IS
$$ This is a function catalog maintained by local payroll modules.$$;

CREATE TABLE deduction_class (
   id serial not null unique,
   country_id int references country(id),
   class_name text,
   primary key (country_id, class_name)
);

COMMENT ON FUNCTION deduction_class IS
$$ Catalog of deduction classes, maintained by each local tax module $$;

CREATE TABLE deduction_type (
    id serial not null unique,
    class_id int references deduction_class,
    account_id int not null references account(id),
    name text not null primary key
);

COMMENT ON TABLE deduction_type IS
$$ Company maintained deduction types.$$;

CREATE TABLE tax_brackets (
    id int not null unique,
    class_id int references deduction_type(id),
    adj_floor numeric,
    adj_ceil numeric,
    tax   numeric not null,
    primay key (class_id, adj_floor, adj_ceil)
);

COMMENT ON TABLE tax_brackets IS
$$ Catalog of tax brackets maintained by individual payroll modules.$$;

Comments?  Feedback?

Chris Trvers