On May 8, 2006, at 3:33 PM, Karen Hill wrote:
What is your favorite front end for end users to interact with your
postgresql db? Is it java, .net, web apache + php, MS-Access, ruby on
rails? Why is it your favorite? Which would you recommend for end
users on multiple OSes?
This is totally dependent on the individual user's preference:
- Many users comfortable with command line shells prefer psql (this
is my choice).
- Others who like a gui might use pgAdmin (or miriad others)
- If interoperability with MSOffice is your main concern, I could see
Access/ODBC being useful. As a general front-end, probably not.
When you start talking about java v. .net v. php vs. ruby, that's a
whole other ball of wax. That's a lot less about interaction, or even
about databases and a lot more about programmer preference.
Personally, I would recommend:
Python + psycopg (highly interactive, great for complex scripts or
even apps)
Java + JDBC + Hibernate (I think JDBC sucks, but Hibernate totally
rocks and more or less hides it from you)
Shell + psql (great for simple reporting, automating dba tasks, etc.
etc)
Also, what do you think of having the database management system do
all
work (business logic etc) with the front end as a user interface vs.
having N-tier with an application server handling business logic, and
the db just accepting data. I currently have the opinion that N-tier
is not as good as having the db contain the business logic via stored
procedures. This comes from having to re-invent the wheel every
time a
new user app needs to be created.
From personal experience (and others will disagree), I find putting
logic in the database to be a bad idea. I only use stored procedures
for triggers and the like, and I try to avoid those whenever possible.
Here are my reasons why:
- I don't like the PL/pgSQL language (yes there are alternatives, but
they have their own drawbacks)
- It's complex to test and upgrade (we actually wrote non-trivial
infrastructure to automate both)
- It's difficult to debug (compared to external languages like python
or java)
- It's difficult to profile, therefore getting good performance can
be difficult
I had a very complex system coded in stored procedures that performed
poorly and was hard to maintain. It's now refactored into java/
hibernate code that's simpler to understand, performs much better and
is easy to extend and maintain. Of course that's just my particular
case and obviously YMMV.
Stored procs could make a lot of sense if you have many different
clients accessing the db in different ways and you want to strictly
enforce business rules across all of them. I had no such requirements
in my case.
In any case I would strongly recommend doing the simplest thing that
you can get away with. If your business rules can be fulfilled with
grants, views and constraints alone, use them.
-Casey
