Re: Proposed schema for payroll

[Thomas Bullock <..hidden..>]

Chris,

My experience is with US payroll only.  That is, I don't know what European, Australian, and other country methods of handling payroll require.

In the US there are different types of income an individual is taxable on.  For example, interest, dividends, royalties, prizes, awards, lottery winnings, fees company Board members are paid, reimbursement of various expenses (moving, travel, etc.), compensation for services rendered (wages, salaries, etc.), and so on.  But most of these are not in a payroll system.  So income types would only be for compensation related types, of which there are but a few.

Since I prepare tax returns for international visitors to the US, there are different rules and forms to use.  For example, there are wages or salaries.  But if some or all the earnings are exempted due to a tax treaty a taxpayer is entitled to, then the US system at year-end issues form W-2 to report gross wages and taxes and other deductions withheld during the year.  If there was a tax treaty, then a form 1042-S is issued to show the amount of compensation that is excluded from taxation for the tax year.  That form also is used to report taxable living expense scholarships (which are not considered compensation and so are not part of payroll).

I guess my point is that whatever payroll system you develop, you also need to categorize income paid in different manners when outputting reports to the governments and taxpayers.   I don't know enough about DB design, so I may have missed that this issue is included in what you presented below.  If not, then some consideration for types of output should be added.

Cheers,

Tom

-----Original Message-----
From: Chris Travers [mailto:..hidden..] 
Sent: Thursday, October 27, 2011 7:20 PM
To: Development discussion for LedgerSMB
Subject: [Ledger-smb-devel] Proposed schema for payroll

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

------------------------------------------------------------------------------
The demand for IT networking professionals continues to grow, and the demand for specialized networking skills is growing even more rapidly.
Take a complimentary Learning@Cisco Self-Assessment and learn about Cisco certifications, training, and career opportunities. 
http://p.sf.net/sfu/cisco-dev2dev
_______________________________________________
Ledger-smb-devel mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel