Re: Integrating LedgerSMB with a member database

Hi Nicolas,

On Wed, Jul 22, 2015 at 11:41 AM, Nicolas Couchoud <..hidden..> wrote:

Dear LedgerSMB authors and users,

I am one of the treasurers of an alumini association. I intent to
modernize our treasury, and I'm considering using LedgerSMB to do that.

Great!

Our association has a PostgreSQL alumni database containing financial
data, such as which alumni paid their subscription, and I would like this
to be linked with our accounting data.

If they are in the same database [as you describe below], that should be possible.

The fact that LedgerSMB also uses
PostgreSQL to store accounting data is very promising from this point of
view.
So I have some questions to ask :

1) To ease integration between alumni and accounting data, I would like to
have them both in the same database. This implies to put accounting data in
a different namespace than alumni data to avoid name clashes. Is this
possible ?

It *should* be possible, but in all honesty, I haven't tested.

If I change the db_namespace variable in
/etc/ledgersmb/ledgersmb.conf, is it enough to ensure that LedgerSMB will
never do anything in namespace "public" ?

That's the intent indeed.

2) In that case, then my intent is to add a transaction in the accounting
data each time a new subscription appears in the alumni data, probably via
a TRIGGER.

That's an option; it would also be possible to keep a table somewhere which lists all subscriptions processed into LedgerSMB so far and process all of the remaining ones in a stored procedure or other program, updating the table with processed subscriptions as you go.

This way, if you find differences, you'll be able to find out *why* there are differences and you won't be left second-guessing.

I have a question though: to me "adding a subscription" means a one time event (until someone "unsubscribes"), possibly with multiple invoice moments ([automatic] subscription renewals) between subscribing and unsubscribing. Are you talking about entering all the subscription renewals into the database when you say "new subscription"? If you mean my interpretation (i.e. not the renewals) then how do you want to deal with the renewals? Do you expect LedgerSMB to generate transactions for you at the appropriate times? [Note: this is possible, but requires a bit of a different approach than what you're saying below.]

After reading some parts of the source, it seems to me that
adding a transaction just consists in :

a) adding a new line in table "gl" ;
b) for each account affected by the transaction, adding a new line in table
"acc_trans".
Is this correct ?

That's correct for some transactions. By which I mean: GL transactions work that way, but AR (Accounts Receivable) transactions don't. AR transactions are usually created when someone owes the association any money so that if there are unpaid accounts, the software can tell you (or anybody else -- your auditor?) who still owes you and how long the account has remained unpaid.

Will this remain true in future versions of LedgerSMB ?

Yes. The same will remain the case for the duration of the 1.4 release. After 1.4, we're not expecting to change the mechanics entirely, but slight changes may be required to the columns of the acc_trans table. Also, at some point in the future, there's a plan to "rename" the acc_trans table to "journal_line" and the "gl" table to "journal_heading" or "journal". But as I said, those will be minor changes that could easily be incorporated into the stored procedures in a matter of minutes.

Regards,

Bye,

Erik.

http://efficito.com -- Hosted accounting and ERP.

Robust and Flexible. No vendor lock-in.