Thread: Application using PostgreSQL as a back end (experienced programmers please)

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). Which combination works best with PostgreSQL? 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 will be investing a significant amount of my spare time into these projects over the next few years and would be grateful for any direction from experienced programmers.

Regards,
Bill Dika

--As of Saturday, November 12, 2005 1:58 PM -0500, Bill Dika is alleged to
have said:

> 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). Which combination works best with PostgreSQL? 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.

--As for the rest, it is mine.

(I'm not sure if this is the best list for this question, but I can't think
of a better one to refer you to.)

What type of front end are you considering using?  Web-based?  Native
Windows?  Cross-platform?  If want to build one, would another type be
useful/possible later?

How do you intend to have people use the program?  Install it themselves?
Run a web-service?  Have an expert install it for them?

Just trying to get some idea of the direction you intend to go with the
program.  You could go with Visual Basic (or it's close cousin, RealBASIC),
as a first suggestion.  That would allow you to use your current
programming knowledge easily.  You just treat PostgreSQL as another OBDC
data source.

I like some of the above languages, but they and others have strengths and
weaknesses for different purposes, and for different thought styles.  Which
is best for your project will depend on the project and yourself.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

Hi Daniel:

Thanks for the quick response.

I intend to do this in Linux, using all open source software. I'm not too concerned with getting it to run in Windows. I only use Windows at work and only rarely do I boot into it at home. I hope to eventually give back something of some value to the open source community. If I can create a useful Linux application at home that is applicable to work, maybe sometime in the future I can get Linux on a desktop at work. We currently have Linux as a file server at work for backup, but all the desktop machines run Windows. Sorry I wasn't clear about this in my initial post.

As for my thought style, I tend to think mathematically and structurally (everything has it's place). After all I'm an accountant, who does a lot of income tax work for clients :-). Ideally I would like a development environment where I can create forms with text box, combo box and list box widgets, something like MS Access or PgAccess but offering a little more power and control.

Are any of the languages or environments I mentioned suitable for this? My initial thought is that a web interface would be a little too limiting. I would expect my users to be able to install without an expert.

Thanks.

Regards,
Bill Dika

On Sun, November 13, 2005 9:19 am, Bill Dika said:
> Hi Daniel:
>
> Thanks for the quick response.

No problem; I happened to have time.   Sorry I couldn't do the quick
feedback.

> I intend to do this in Linux, using all open source software. I'm not too
>  concerned with getting it to run in Windows. I only use Windows at work
> and only rarely do I boot into it at home. I hope to eventually give back
>  something of some value to the open source community. If I can create a
> useful Linux application at home that is applicable to work, maybe
> sometime in the future I can get Linux on a desktop at work. We currently
> have Linux as a file server at work for backup, but all the desktop
> machines run Windows. Sorry I wasn't clear about this in my initial post.

No problem.  I just asked to clarify the problem space.

> As for my thought style, I tend to think mathematically and structurally
> (everything has it's place). After all I'm an accountant, who does a lot
> of income tax work for clients :-). Ideally I would like a development
> environment where I can create forms with text box, combo box and list
> box widgets, something like MS Access or PgAccess but offering a little
> more power and control.

This will be where you are going to have to work: There are toolkits that
give you the widgets to work with, but the graphical form creation is a
function of the IDE more than anything else.  So, to get it, you need a
language that is closely-tied to an IDE.

> Are any of the languages or environments I mentioned suitable for this?
> My initial thought is that a web interface would be a little too
> limiting. I would expect my users to be able to install without an
> expert.

Java is probably closest to what you want, though having just learned it
for my job I can say I think it is needlessly complicated.  I like Perl,
and you could do this in Perl, but you'll be laying out forms in code (not
graphically), and Perl takes experience and discipline to write good,
maintainable, code in.  (If you decide to try Perl, read 'Perl Best
Practices', it'll help.  Heck, read it anyway.  It's a good book.)

I've been looking at Ruby recently, and would recommend it but the toolbox
isn't as mature.  Still, it has most of the flexibility of Perl, while
aiding more in writing structured code.  You would be laying out the forms
in code here as well.  (Though if you wanted a web interface Ruby on Rails
looks amazing.)

In Java, with the right IDE, you might be able to avoid laying out the
forms in code, but that is because of the IDE, not the language.  I'd
expect the interface to end up mostly done in code, no matter what
language you end up in.  (Unless you use Visual Basic, a clone, or one of
the C-based, platform-specific, IDE toolchains.)

Java is probably the easiest to create an 'installer' for: Just package
everything in a .jar file.  Perl's CPAN is easy to use as well, but it is
harder to create the package for.  Running code while programming/testing
is the opposite way around: Java is several steps, and you have to get the
classpath right, where Perl can usually just run it.

Ruby is as easy to run, but I don't know if it has any particular
installation system.

I'd probably reluctantly recommend Java for this, right now.  I'd like to
recommend Ruby, but I'm just not sure if it has the tools you'll want.
(It may, but I don't know that.)

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

On Saturday 12 November 2005 10:58, Bill Dika wrote:
>... 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....

If you are interested at all in cross-platform you might look at Qt
(already included with many Linux distros). It has all the widgets
and PG connectivity is not a problem.

You seem negative on web-based solutions but I don't imagine that
tax-prep requires much more than what a browser has to offer. If you
need drag 'n' drop or to dynamically populate fields, drop-downs,
etc., look into what AJAX can let you do.

Cheers,
Steve

> Hi Daniel:
>
> Thanks for the quick response.
>
> I intend to do this in Linux, using all open source
> software. I'm not too
> concerned with getting it to run in Windows. I only
> use Windows at work and
> only rarely do I boot into it at home. I hope to
> eventually give back
> something of some value to the open source
> community. If I can create a
> useful Linux application at home that is applicable
> to work, maybe sometime
> in the future I can get Linux on a desktop at work.
> We currently have Linux
> as a file server at work for backup, but all the
> desktop machines run
> Windows. Sorry I wasn't clear about this in my
> initial post.
>
> As for my thought style, I tend to think
> mathematically and structurally
> (everything has it's place). After all I'm an
> accountant, who does a lot of
> income tax work for clients :-). Ideally I would
> like a development
> environment where I can create forms with text box,
> combo box and list box
> widgets, something like MS Access or PgAccess but
> offering a little more
> power and control.
>
> Are any of the languages or environments I mentioned
> suitable for this? My
> initial thought is that a web interface would be a
> little too limiting. I
> would expect my users to be able to install without
> an expert.
>
> Thanks.
>
> Regards,
> Bill Dika

hi bill,

i develop db front ends with php / html / Lemos' forms
class and css.  i use adodb to communicate with a
pgsql the backend.  SesameStreet.org uses php / adodb
and postgresql to power their site.

i'd be happy to help you get started with this
methodology if you are going to gpl your project and
this type of interface interests you.  creating text
boxes, select boxes, list boxes, linked select boxes
and text areas are a snap.

i'm not up to speed on javascript and ajax.  maybe
someone else is, though.

i can spend some time actually coding interfaces, too,
however, i can't commit to a definite amount of time
since my life is chaotic.  ;-)

let me know if you are interested enough to
investigate further.



__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com

Thanks for all your responses. You have given me much to think about.

Thanks for the offer operationsengineer1@yahoo.com. I will google some of the terms in your post (adodb?, Lemos' Forms?) and get back to you.

Ilias, I think that C# would take me too long to learn (if I could learn it at all ;-)). The same goes for Java, which I understand is a difficult language. My strengths are in accounting and income tax. I have only taken one computer course in my life and that was an introductory course to Linux. I am self taught in computers and can only devote spare time to this project. Everything I've heard about C and its associated languages suggests power and portability but with those features comes a steep learning curve. Thanks, Ilias, for replying to my post.

Hi Steve. I will look into Qt. Also I will rethink a browser interface. Where I work we have some professional income tax software that runs in Windows (and is quite expensive). It does not use a web interface. Furthermore, I think that if it did use a web interface, it would not be nearly as responsive as it is. That is why I was thinking of not using a browser. I use SQL-Ledger for my own bookkeeping. It is written in Perl, uses PostgreSQL as a backend and a browser for a front end. While I like the program very much and enjoy the freedom of using it as compared to something like say, Quickbooks, there is a noticeable delay when loading a form. This delay seems to be much less in stand alone programs that do not use a browser. Or am I wrong about this? In any case your suggestion, along with the response from operationsengineer1@yahoo.com has caused me to rethink using a web based solution.

Thanks again for all your responses. They are much appreciated.

Regards,
Bill Dika

Bill Dika wrote:
> Furthermore, I think that if it did use a web interface, it would not be
> nearly as responsive as it is. That is why I was thinking of not using a
> browser. I use SQL-Ledger for my own bookkeeping. It is written in Perl,
> uses PostgreSQL as a backend and a browser for a front end. While I like
> the program very much and enjoy the freedom of using it as compared to
> something like say, Quickbooks, there is a noticeable delay when loading
> a form. This delay seems to be much less in stand alone programs that do
> not use a browser. Or am I wrong about this? In any case your

Basically, the issue with web-based is more of a perception/workflow
problem than performance. Everything is submit->server does
stuff->return results. While you can sprinkle a little Javascript to
make the UI more responsive, using too much Javascript tends to make
your programs hard to maintain.

At least that is how it was before. Now with AJAX, you can background
submit data the moment your user clicks/types something and while
they're still doing their data entry, the server is already processing
their work to either return results faster or update the screen realtime.

A good example of this is comparing Mapquest to Google maps. Mapquest
uses the old paradigm. You click on a map, the mapquest server
resizes/zooms/whatever and creates a new gif for you to download. Google
maps on the otherhand is constantly generating new data while you
navigate allowing what appears a "windows-like" interface.

You definitely have to do much better planning to write AJAX web apps
though. I'm still learning the nuances myself -- trying to figure out
where's the best place to use it in my web apps. I.e. don't go overboard
immediately and try to implement it everywhere. Just use it where the
user perceives UI delays.

Of course, there's the idea of not bothering with AJAX and just living
with the perceived delays. It ends up not being a big deal because (1)
users expect such delays on the web and (2) users get used to such
delays on the web. It was a bit of a concern when we developed our
insurance app as 100% web-based -- users accustomed to switching to
different pages of info instantaneously going to 1 second just for the
browser/submit/return latency. But it ended up being not an issue. Users
adapt, especially if you take advantage of what web apps can do. For
example, use linking so users can go from a page of info/form entry to
multiple ones to either lookup reference info or fill out data needed
beforehand. Users appreciate slower single-click functionality versus an
extremely responsive UI that requires multiple clicks to get into
different areas and back.

On Wed, Nov 16, 2005 at 08:02:52 -0800,
  William Yu <wyu@talisys.com> wrote:
>
> Of course, there's the idea of not bothering with AJAX and just living
> with the perceived delays. It ends up not being a big deal because (1)
> users expect such delays on the web and (2) users get used to such
> delays on the web. It was a bit of a concern when we developed our
> insurance app as 100% web-based -- users accustomed to switching to
> different pages of info instantaneously going to 1 second just for the
> browser/submit/return latency. But it ended up being not an issue. Users

Option 2 also works for people who don't enable javascript.
I have to really need to use a site before I will enable javascript to use
the site instead of walking away. Even in that case I will often examine the
javascript code to see if I can bypass it without having to enable to get
things done. (As an aside, doing this I have found some sites that only
validate data on the client and not on the server.)

Christopher Browne <cbbrowne@acm.org> writes:
>> 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.

Actually, this merits a bit more explanation...

The thing that made Prolog desirable, to me, for this application, and
which seems to me to make the database engine likely unsuitable is the
whole issue of "ordering of calculations."

In Prolog, I didn't have to say *anything* about what order the
various tax rules needed to be applied in order to calculate the final
results.

You'll find that expressly applying the logic to do all of the
calculations in proper order will be one of the irritating parts of
the application.

Doing the calculations inside the database engine would mandate doing
some fancy SQL indeed in order to ensure that:
 a) All calculations are done in the right order
 b) You have all of the interim balances presented to you...

This is essentially the same problem as that which spreadsheets have
in determining the "graph" of dependancies of one cell against
another...
--
(format nil "~S@~S" "cbbrowne" "ntlug.org")
http://www.ntlug.org/~cbbrowne/spreadsheets.html
How does the guy who drives the snowplow get to work in the mornings?

> 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

Hi Chris:

Many thanks for your input. I've bookmarked your pages and will try your program (my initial attempt to download it from sourceforge was unsuccessful). I am a Canadian chartered accountant and we use Intuit Profile at work. I have some familiarity with the complexities of applying income tax rules and calculations and have created some spreadsheets to calculate personal income taxes for limited situations.

I thought that I would use Postgres tables to store all the inputs in a tax return (linked to forms) and use plpgsql or plpython functions to do the various calculations. I was thinking of one large master function that calculates the actual taxes and takes as input various other functions calculating subtotals from each form etc. The master function would take care of the calculation ordering. Each change in an input field would trigger a recalculation of the input field's own supporting function(s) as well as a recalculation of the master function. I was wondering how slow this might be. I never intended to use a table field to store calculated results, (I was under the impression that it was incorrect database design) but you have given me something to think about. Also I think I need boolean fields to store the many Yes/No answers on forms that impact on the calculations.

I haven't used Postgres before so I'm not sure if what I want to do is possible and/or advisable. I see that you are advising against calculating inside of Postgres. What language would you suggest I use to create the 'calculation engine'? How can that language get the info from Postgres? Would you recommend Python? Can Prolog be used with PostgreSQL? Do I assume that you are recommending that I use Prolog for this type of project and forget about Postgres? Is it a difficult program to learn?

I didn't know that income taxes was a 'holy grail' to linux enthusiasts. The only reason I have been thinking of doing a program myself was that I couldn't find anything on the internet regarding linux and Canadian income taxes and secondly, I have some experience with the subject matter.

Thanks again for your info. Please keep in touch and let me know if I can be of any assistance to you or any other linux enthusiast that you know of who may be dealing with this matter.

Regards,
Bill Dika
London, Ontario Canada

Thanks Nikola.

Your explanation of layering was helpful. The consensus from you experts seems to be that I should keep the business rules and logic out of the database, so I will do that.

As far as using Openoffice goes, I don't think it will provide enough flexibility and power in the design of forms and reports. I've looked at the Base forms and reports capabilities in Openoffice 2.0 and don't think that it is even up to the abilities of MS Access. And MS Access does not have the form capabilities that I will need. Besides that it (Access) can't be GPL'd. For business logic, I'm leaning towards Python (because of its computational abilities) and Tkinter or wxPython for a GUI. Either that or PHP and a web interface.

BTW. Why are Canadian income taxes so complicated? I don't know. But I can tell you that I've been a Canadian professional accountant for about 25 years and the Canadian income tax act gets more complicated every year.The government is continually adding new provisions and grand-fathering out older provisions. Income taxes in Canada are not used solely to finance the government. They are used to stimulate certain sectors of the economy, to redistribute wealth and to provide various incentives to individuals and corporations for any number of reasons (for example there are significant tax incentives to get individuals to save for retirement). All this adds to the complexity of the tax provisions. In any case, I imagine that many countries use their income tax system for more than 'just financing the government'. :-)

Hi Michael:

Thanks for the tip but I already use SQL-Ledger for my accounting needs. I am talking about a program to calculate personal income taxes.

Regards,
Bill Dika

Bill Dika wrote:
> Thanks Nikola.
>
> Your explanation of layering was helpful. The consensus from you experts
> seems to be that I should keep the business rules and logic out of the
> database, so I will do that.
>

Hi,

Just a small note here. I've seen projects where exactly everything is
put into the application, including validations of data and such. The db
was basically a bit bucket with indexes. That turned out to be a
complete disaster. It's much harder to keep track of all validations in
the java/whatever code than letting the db do this for you.

It's very easy to add constraints, foreign keys and such to a db and
that should always be done. There are no excuse what so ever not to.
Everything you can validate (via CHECK(), fk, trigger, whatever) do it.
A db with errors is a nightmare to patch. It's much better to have the
application tell you it won't do an operation and you do a fix rather
than the application inserting errors silently.

Ok, this was the introductionary chapter of any db book. Sorry about
that. ;-)

Best regards,
Marcus

Thanks Marcus. I intended to make full use of Postgres' constraints, primary key, foreign key and trigger capabilities to keep the integrity of the data. And yes, I have read a few db books :-), but you didn't know that for sure. Thanks for the advice.

Regards,
Bill Dika

mengpg@engene.se (Marcus Engene) writes:
> Bill Dika wrote:
>> Thanks Nikola.
>> Your explanation of layering was helpful. The consensus from you
>> experts seems to be that I should keep the business rules and logic
>> out of the database, so I will do that.
>
> Just a small note here. I've seen projects where exactly everything is
> put into the application, including validations of data and such. The
> db was basically a bit bucket with indexes. That turned out to be a
> complete disaster. It's much harder to keep track of all validations
> in the java/whatever code than letting the db do this for you.
>
> It's very easy to add constraints, foreign keys and such to a db and
> that should always be done. There are no excuse what so ever not
> to. Everything you can validate (via CHECK(), fk, trigger, whatever)
> do it. A db with errors is a nightmare to patch. It's much better to
> have the application tell you it won't do an operation and you do a
> fix rather than the application inserting errors silently.
>
> Ok, this was the introductionary chapter of any db book. Sorry about
> that. ;-)

There are multiple sorts of "business logic"; this is a pretty eminent
example.

I'd say that "yeah, having constraints, foreign keys, and such would
seem good here."  Starting with a database schema that prevents the
data from getting invisibly unclean seems a good thing.

The "business logic" that's really in question is of where/how to
perform the tax calculations.  It seems to me that "inside the DBMS"
is quite likely not to be the right answer.
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/linuxxian.html
"In most  countries selling harmful  things like drugs  is punishable.
Then how come  people can sell Microsoft software  and go unpunished?"
-- <hasku@rost.abo.fi> Hasse Skrifvars