Re: some questions about PostgreSQL in general

Jules,

> > Darn! Too bad you're not in my area. This is exactly the kind of
> work
> > my company does ...
>
> yes, that's too bad indeed. support is hard to find over here, we're
> located in the southern part of the netherlands.

Well, if you get desperate, we could always set up some kind of
online-only support thing. Possibly in coordination with PostgreSQL
Inc. I'm not sure how their experience with Netware/Firewalls rates.

> yes, i know. i don't know much about firewalls, SSL, etc. (my network
> background is mostly NDS, netware, IPX), the best decision is IMO to
> consult an external company for the setup and have another one audit
> for security. that gives me more time to focus on the central issues
> like programming, db design etc.

Yes. And you're more likely to find a network security company in the
Netherlands than a PostgreSQL or PHP one.

> well, i guess i'm lucky: we don't use windows apps for business
> critical apps. the main app was in clipper 5.3b which is very stable.
> the reason we dump it, is that it's language oriented, not db
> oriented,
> and it's no longer developed.

And there's that pesky Y2K bug. :-)

> OK, i have (and already had) that etched into my mind. we had a 2
> year
> pre-build stadium in which we (management team + management)
> evaluated
> our company and what we expect from our systems.

This sort of ties in with your question about table re-design. There
are some limitations to altering existing tables ... for example,
dropping a column requires a table re-build (adding or re-naming a
column, and adding constraints, are permitted). However, in my
experience, significant changes to the main data tables in a running
system usually require redesign of a number of components, so the table
re-build is less arduous than, say, checking all of your scripts for
references to the dropped column.

Overall, it's better to get the data design nailed down to the 90% level
before you write any SQL. And this is true regardless of DB system.

> after some lurking on the postgresql mailinglists there seem to be a
> few items in postgresql which should be avoided, like the array
> datatype and BLOBs. i guess it would be good to keep the design of
> the
> db as straightforward and elegant as possible and avoid more exotic
> features...

Yes, there's chronic problems throughout the industry with the
"advanced" data types (MS SQL Server has much worse problems with BLOBs
than Postgres). Partly it's that these types were not in the SQL 92
standard, and some (BLOBs) depend heavily on the host filesystem, so
that implementation is RDBMS- and OS-specific. Some DB theorists,
notably C.J. Date and F. Pascal, are opposed to the use of non-atomic
data types in any form and their arguments have significant merit.

> as for the development time, maybe it's a good idea to build a more
> sophisticated front-end for the power users in Java, and a quick-and-
> dirty HTML frontend with PHP. no offence meant, maybe in the end it
> will be PHP only.

Ah. I did not make myself clear. In the PHP-vs-Java comparison, you
have two different layer structures:

Web Server
|
PHP + HTML
|
Postgres Functions, Views and Triggers
|
Data tables

or:

Web Server
|
JSP Engine + HTML
|
J2EE or Java Middleware
|
Postgres Functions, Views, and Triggers (optional)
|
Data Tables

The difference here, between PHP and Java, is that Java includes an
extra, robust middleware layer capable of sophisticated, modular
business logic and data management across several servers, if desired.
Also, display/interface programming (JSP) is seperated from business
logic (J2EE or java libraries) making maintenence easier. The
PHP/Postgres Function structure, while quite economical for rapid
development of small database applications, lacks this robustness and
flexibility. It's a development cost vs. long-term value argument, and
I've done projects on both sides of the argument.

BTW, you can do the same things using Perl::DBI as you can with Java.
I'd also love to hear from a Python fan, if we have any on the list.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco