Optimal Postgres Development Process, Software

Hi,

First a little background:

I am an electrical engineer and have had a decent amount of
experience programming - but mainly languages like matlab, C++, pascal, VB. Over the last few years I have developed and maintained an ms access database for a business. It had up to 3 or 4 users at any one time, usually two.

It was arranged in a split fashion, with a front end and a back end.
Although the backend reached between 10-20 MB in size, it was
extremely slow at times because of the involved business logic in
some of the queries. Some reports were based on > 30 queries,
including VB procedures.

Unfortunately, I had a cheapskate boss and was not permitted to get a
proper database. This was also 5 years ago and I was learning
databases from scratch.

As a result of that experience, I am in the position where I am going
to have to craft a new database from scratch to do the job of the
old. After several weeks/months of searching, I have decided on
Postgres as a backaend after starting to be skeptical of the MySQL
hype machine - it seems like a toy database for what I want. I also
have a pretty good idea what I want from Postgres, although anything
I haven't taken into consideration I'd like to know.

I have also spent a while searching the mailing list archives and
also just browsing the last 6 months of postings to try and find an
appropriate answer.

Please note: I will NOT be using my old Access frontend - I will
create a new frontend from scratch.

So, here are my requirements:

1. I don't want to be stuck with a product that is a pain in the ass
to update with a developer that could leave me stranded, as was the
case with MS Access 97. I want something that will only require me to
do work when the business needs changes, not as a result of another
company's need to design in obsolescence. I.e. Access as a frontend
will be a last resort.

2. I don't want to pay money to a company for no good reason (i.e.
M$) just because they want a regular income stream from me.

3. I want something that I can look at several years from now and
understand it with a minimum of hassle. Something self documenting
would be preferable, or at least something that is easy to document
or is easy to get an overview of. (Since there is a possibility that
one day I might inherit the business, I have the opposite need of
most employees: rather than make myself indispensible I want to make
the database I create something that any competent person can
understand/extend in the future, be it me or someone else.

4. I want something secure; I don't want just anybody accessing this
information.

5. I want something robust; I want something that I can get backups
made of regularly and will only die if the IT department does a poor
job of making sure the supply of power and quality of components is
not up to par.

6. I want something scalable; I don't want to be switching software
halfway down the track because I have 50 concurrent users instead of
3, or the database grows in the number of rows it has, and somehow a
query does not want to ever finish executing because of this. (As a
part of this, I will attempt to do as much work as possible on the
server end rather than client end, e.g. using views to get the
underlying queries the front end relies on.)

7. I want it to be easy to idiot proof it; I want to make it hard for
the end user to break it. E.g. Referential integrity, cascading
deletes, stuff the MS Access relationships window was able to do
well. Even the lookup tables, where adding a category involved the
user having to create a new category with a new autonumber instead of
just typing a word (leading to multiple instances of the same thing).

8. I want to be able to create reports and forms quickly and easily,
and be able to grok them easily in the future so that I can change
them or at least identify where they are.

9. I want to be able to create the queries (or SQL) the reports are
based on quickly and easily. I don't want to compromise on easy
querying at the expense of complex queries (which I suspect that MS
QBE was, though I could be wrong).

10. I want it to be easy to debug; I want to be able to logically
identify where something is going wrong so that I can fix it when it
breaks.

I figure that half the answer is using Postgres as the database
engine, and pushing as much business logic as possible onto it, as it
should be much faster to compare different chunks of RAM than to
send stuff back and forth on a network. I will continue with good
normalization procedure as per Codd, in the same way as I did with ms
access and table design. If I am wrong with any of this, please
suggest an alternative, I doubt I will get anyone telling me to use
something besides Postgres, this is after all a PG mailing list. :)

Now for the other half... how to do the development work as per my
requirements? What software would best satisfy my list of ten
features?

I'd also like to know the process involved... if there is a tradeoff
between learning curve and approaching my ideal development platform
as stated in items 1-10, I'd like to know it.

From the polls I have seen at the postgres site, it seems that
Jasper Reports are most popular for the reporting needs part of my
equation. However, as with Postgres vs MySQL, judging things by number of votes needs to be done with qualifications. No sense in blindly following the herd over a cliff.

http://www.postgresql.org/community/survey.45

I would also like to know how best to map my process for development from ms access to Postgres + whatever you recommend. To assist you, my process for development in Access was roughly (from memory):

1. Figure out what tables I need and which columns are needed in the
tables. Do this on paper.

2. Create the said tables on the access backend.

3. Open up the relationships window, view the tables I had just
created and drag links across, then decide one to many etc, cascading
deletes/updates etc.

4. Open up the tables again and do things like add indexes, constrain
user input as appropriate in particular columns, etc, set up lookup
tables etc.

5. Create some input forms, enter some test data, see if they work
ok. If necessary, spend ages on the internet/ ms access help/
computer books looking for how to implement exactly what I want to do
with VB.

6. Create queries needed by reports. See if test data works out ok
and if it is my query or underlying table structure that is causing
problems if it doesn't (usually it is the query, as I was pretty good
at designing the tables). Debug the queries. If necessary, spend ages
on the internet/ ms access help/ computer books looking for how to
implement exactly what I want to do with VB.

7. Create the reports that go on top of the final query just debugged.

8. When a report breaks down, find the underlying query and keep
going down until the previous query doesn't give an error, then fix
the error.

This was all done within MS Access (except the first step, which was on paper).

I'd be curious how people do much the same thing while using Postgres
and either Access or other aftermarket software. Even with the
technical documentation of Postgres, the tutorial etc. I don't get a
good feel for what the analogues of the various processes I did
before are.

I think it would help not only me but anyone else who is
deciding to upgrade, as there are lots of Access holdovers in the same boat.

Lastly, any books to recommend? I have printed out the latest online
PG documentation plus ordering in "Beginning Databases with
PostgresSQL: From Novice to Professional 2E", as that seems to get the best reviews on Amazon.

So, in summary:
1. What software/languages to use for everything? (Even text editors,
if necessary.)
2. What development process to use? How do each of my steps (and any additional ones you might think of) map from Access to your solution?
3. If there is a learning curve/development speed (or something else)
trade-off decision to be made depending on software/language choice, what is it?
4. Recommended books/resources?

Thanks in advance,
pgsqln00b
p.s. Hope I haven't left anything out... ;)

--
___________________________________________________
Play 100s of games for FREE! http://games.mail.com/