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...
create table im_invoices (
-- who should pay?
customer_id not null
-- who get paid?
provider_id not null
constraint im_invoices_nr_un unique,
invoice_status_id not null
invoice_type_id not null
-- 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 (
currency char(3) references currency_codes(iso),
-- variable or fixed costs?
-- cost has been split into several small cost items?
check redistributed_p in ('t','f'),
-- points to its parent if the parent was "distributed"
-- "real cost", "planning" of "quote or purchase order"