SF.net SVN: ledger-smb:[4552] trunk/sql/Pg-database.sql

[..hidden..]

Revision: 4552
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4552&view=rev
Author:   einhverfr
Date:     2012-03-24 12:07:11 +0000 (Sat, 24 Mar 2012)
Log Message:
-----------
payroll db schema is now complete.  Next needs stored procedures and workflows

Modified Paths:
--------------
    trunk/sql/Pg-database.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2012-03-24 10:49:49 UTC (rev 4551)
+++ trunk/sql/Pg-database.sql	2012-03-24 12:07:11 UTC (rev 4552)
@@ -908,7 +908,7 @@
        ('Non-cash');
 
 CREATE TABLE payroll_income_type (
-   itype_id serial not null unique,
+   id serial not null unique,
    account_id int not null references account(id),
    pic_id int not null,
    country_id int not null,
@@ -919,6 +919,17 @@
               references payroll_income_class(id, country_id)
 );
 
+CREATE TABLE payroll_employee_class (
+   id serial not null unique,
+   label text primary key
+);
+
+CREATE TABLE payroll_employee_class_to_income_type (
+   ec_id int references payroll_employee_class (id),
+   it_id int references payroll_income_type(id),
+   primary key(ec_id, it_id)
+);
+
 CREATE TABLE payroll_deduction_class (
    id int not null,
    country_id int not null references country(id),
@@ -942,12 +953,35 @@
 
 CREATE TABLE payroll_report (
    id serial not null primary key,
-   ec_id int not null references employee_class(id),
+   ec_id int not null references payroll_employee_class(id),
    payment_date date not null,
    created_by int references entity_employee(entity_id),
    approved_by int references  entity_employee(entity_id)
 );
 
+CREATE TABLE payroll_report_line (
+   id serial not null unique,
+   report_id int not null references payroll_report(id),
+   employee_id int not null references entity(id),
+   it_id int not null references payroll_income_type(id),
+   qty numeric not null,
+   rate numeric not null,
+   description text,
+   primary key (it_id, employee_id, report_id)
+);
+
+CREATE TABLE payroll_pto_class (
+   id serial not null unique,
+   label text primary key
+);
+
+CREATE TABLE payroll_paid_timeoff (
+   employee_id int not null references entity(id),
+   pto_class_id int not null references payroll_pto_class(id),
+   report_id int not null references payroll_report(id)),
+   amount numeric not null
+);
+
 --TODO:  Add payroll line items, approval process, registry for locale functions, etc
 -- Begin rocking notes interface
 CREATE TABLE note_class(id serial primary key, class text not null check (class ~ '[[:alnum:]_]'));

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.