Skip site navigation (1) Skip section navigation (2)

Optimal Postgres Development Process, Software

From: "Roger Rasmussen" <pgsqln00b(at)australiamail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Optimal Postgres Development Process, Software
Date: 2006-08-15 10:40:17
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice

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 

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 

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 

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.

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,
p.s. Hope I haven't left anything out... ;)

Play 100s of games for FREE!


pgsql-novice by date

Next:From: Mike NeesDate: 2006-08-15 15:02:27
Subject: COPY command
Previous:From: ben sewellDate: 2006-08-15 08:52:23
Subject: Return type for procedure

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group