Hi,
pretty boring stuff for most of you probably, but quite exciting if you're running your own company...
We have started working on extending the existing Invoicing module or Project/Open (http://www.project-open.com/product/modules/finance/) to include Quotes, Purchase Orders and Provider Bills. Also, we want to integrate this with travel costs and timesheet management to truely capture all costs of a project in order to calculate profit and loss on a per-project base.
Below please find the SQL definition of "im_invoices" as it is being used right now. Further below you can find a proposal for "im_costs" to be used as the base class for "im_invoices" in the future.
What do you think about the approach with "im_costs" as a superclass? I guess there are some very experienced database designers amongst the members of the OpenACS community...
Frank
mailto:frank_dot_bergmann_at_project_dash_open_dot_com
http://www.project-open.com/
create table im_invoices (
invoice_id integer
constraint im_invoices_pk
primary key
constraint im_invoices_id_fk
references acs_objects,
-- who should pay?
customer_id not null
constraint im_invoices_customer
references im_customers,
-- who get paid?
provider_id not null
constraint im_invoices_provider
references im_customers,
customer_contact_id integer
constraint im_invoices_contact
references users,
invoice_nr varchar(40)
constraint im_invoices_nr_un unique,
invoice_date date,
due_date date,
invoice_currency char(3)
constraint im_invoices_currency
references currency_codes(ISO),
invoice_template_id integer
constraint im_invoices_template
references im_categories,
invoice_status_id not null
constraint im_invoices_status
references im_categories,
invoice_type_id not null
constraint im_invoices_type
references im_categories,
payment_method_id integer
constraint im_invoices_payment
references im_categories,
payment_days integer,
vat number,
tax number,
note varchar(4000)
);
-- Costs is the superclass for all financial items such as
-- Invoices, Quotes, Purchase Orders, Bills (from providers),
-- Travel Costs, Payroll Costs, Fixed Costs, Amortization Costs,
-- etc. in order to allow for simple SQL queries revealing the
-- financial status of a company.
--
-- Costs are also used for controlling, namely by assigning costs
-- to projects, customers and cost centers in order to allow for
-- (more or less) accurate profit & loss calculation.
-- This assignment sometimes requires to split a large cost item
-- into several smaller items in order to assign them more
-- accurately to project, customers or cost centers ("redistribution").
create table im_costs (
cost_id integer
constraint im_costs_pk
primary key,
constraint im_costs_item_fk
references acs_objects,
name varchar(400),
cost_status_id integer
constraint im_costs_status_fk
references im_categories,
cost_type_id integer
constraint im_costs_type_fk
references im_categories,
project_id integer
constraint im_costs_project_fk
references im_projects,
customer_id integer
constraint im_costs_customer_fk
references im_customers,
asset_id integer
constraint im_costs_asset_fk
references im_assets,
due_date date,
payment_date date,
amount number(12,3),
currency char(3) references currency_codes(iso),
-- variable or fixed costs?
variable_type_id integer
constraint im_cost_variable_fk
references im_categories,
-- cost has been split into several small cost items?
redistributed_p char(1)
constraint im_costs_var_ck
check redistributed_p in ('t','f'),
-- points to its parent if the parent was "distributed"
parent_cost_id integer
constraint im_costs_parent_fk
references im_costs,
-- "real cost", "planning" of "quote or purchase order"
planning_type_id integer
constraint im_costs_planning_type_fk
references im_categories,
description varchar(4000),
);
