Re: Application using PostgreSQL as a back end (experienced programmers please) - Mailing list pgsql-novice

> Hi: I'm an accountant, not a programmer, but I've written a time
> keeping application using Visual Basic for Applications and
> Microsoft Access. I've also done some VBA programming in Microsoft
> Excel.  I eventually (some time in the distant future) want to write
> a Canadian open source income tax program for individuals using
> PostgreSQL as a back end and a GUI front end.  In the meantime (to
> teach myself), I want to create a program to keep track of the cost
> basis of shares traded on the North American stock exchanges. I
> would use this program for work, to keep track for income tax
> purposes, of the cost of shares over time to our clients. Something
> we currently make do with using a flat file database in MS Excel.
> My Problem:

> I have introductory books on Python, Perl and Tcl/Tk. I feel
> comfortable setting up the database in PostgreSQL, but in creating
> the GUI front end, I'm not sure how to invest my time. Should I use
> Tcl/Tk, Python with Tkinter or wxPython (using BOA Constructor?),
> Perl with ???. Which will get me most productive in the shortest
> amount of time (considering I'm not a programmer).

All of these are plausible; in a way, I think you'll find it not
HIGHLY relevant which you choose, as what you'll wind up building will
be extremely repetitive.

Form after form, what you'll have is:
  1.  Forms, which have sets of fields;
  2.  Fields, some of which are informational text, others with
      data...
       - Sometimes the data is filled in (e.g. - data that you'll
         store)
       - Sometimes the data is calculated from other data on the
         form
       - Sometimes the data is a copy of data on some other form

I really think you'll want to construct a computer language for
representing the forms and their contents; with the huge volume of
forms that the government offers, that's just natural.

It is entirely likely that the hierarchy of forms would fit nicely
into a table.

create table forms (
   form_name text not null,   -- e.g. - T1, T4, T5, Twhatever
   title text,
   --- probably some other fields
   primary key (form_name)
);

create table fields (
   form_name text not null,   -- e.g. - T4
   field_name text not null,  -- e.g. - line 7, line 7(a), ...
   vert_order integer,
   horiz_order integer,
   type integer,    -- 1 = text field, 2 = enterable value,
                    -- 3 = copied from another field,
                    -- 4 = calculated from other fields...
   text_value text,  -- Populated IFF type = 1

   source_form text, -- populated IFF type = 3
   source_field text,

   ... cleverness falls in having a good representation for
       4 - calculating from other fields ...

   primary key (form_name, field_name)
);

Note that TaxPrep and the like used to have each form be a file that
consisted of some of the above sort of information.

These days, the headlong rush to make "everything XML" probably means
that QuickTax and TurboTax and the like represent forms as XML
documents (which, I should point out, will represent a "little
language" for representing the forms).

Stowing it all in a couple or three tables is the "database way" :-).

PostgreSQL supports the notion of inherited tables; the sample case is
to define a "city" table, and then inherit, from that, a "capital"
table which is a more specific sort of city.  That would be not a bad
way of dealing with having multiple kinds of fields on forms; you'd
have a 'base' field table that has a description, and then have a
couple of inherited tables:

 - One for values that are to be "typed in" (which presumably means
   that this is the one table that has to assign database table
   storage for the data to be entered)

 - One for values "copied from somewhere else"; this needs to
   reference other fields/forms

 - One for values calculated from other values on the form.

   It might be worth breaking this down into several cases which
   occur VERY frequently; fields are generally either:
    - A sum of values from several fields on the form;
    - The difference of two fields on the form, often with 0 as minimum
    - A multiple of one field on the form

> Which combination works best with PostgreSQL?

I'd consider that the "not important" part.  I would contend that once
you have an example of each kind of field that you want to represent
(where I suggested about 5 examples), you see how those are each
represented on screen.  Once you've done one field (of each variety),
they'll all be the same.

The various GUI frameworks are trying to accomplish mostly the same
sorts of things; the differences shouldn't be too frightening.

> I expect the income tax program to be computationally intensive but
> I also expect most of this computation to take place in PostgreSQL
> and not the front end.

I disagree, and I have some history with this variety of application
;-).

My personal tax calculator is an application I wrote in Prolog.  The
"base data" is a surprisingly compact set of Prolog rules.
<http://sourceforge.net/projects/prologtaxes/>

What is really vital in the application is how you store the rules for
expressing the interrelation between fields.  What you're sure to need
to do is to draw the data out in order to do the calculations.  I'd
fully expect you to have some sort of "calculation engine" where the
calculations get done.  This may seem surprising, but I really think
it won't take place inside the database.

You're looking to construct what has historically been one of the
"holy grail" things amongst Linux enthusiasts; I have collected some
of the ideas from both slightly successful attempts and from (more
typically) futile blathering from people that really didn't have a
clue what they were getting into :-(.

<http://linuxdatabases.info/info/freetaxsoftware.html>
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://linuxdatabases.info/info/slony.html
"They laughed at Columbus, they laughed at Fulton, they laughed at the
Wright brothers.  But they also laughed at Bozo the Clown."
-- Carl Sagan