Database Design & Application Server Design

[Lane Beneke]

All,

New to the list and a relative newbie to PostgreSQL. Please forgive stupid
questions.

Designing an application server for a work order processing (et al)
database. I have a good handle on sequences, referencial integrity, views,
& the basics. What books/documentation would you recommend to learn more
about...

- database design for security
- database design for performance
- database design for ease of maintenance
- triggers and functions

Also, the application will require some complex forms, and printing
graphical reports (currently served from an Access 2000 database.) What
tools/interfaces/servers would you recommend for the application server?

Is there a tool you would recommend for generating graphical reports to
html?

TIA.

Lane Beneke; IT Manager
Stephenson Millwork Company, Inc.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Richard Huxton]

On Wednesday 05 November 2003 19:42, Lane Beneke wrote:

Designing an application server for a work order processing (et al)
database. I have a good handle on sequences, referencial integrity, views,
& the basics. What books/documentation would you recommend to learn more
about...

- database design for security
There are plenty of resources on designing securely and the principles are the
same for databases as anything else:
- decide what operations to permit and allow only that
- only allow validated input into your application/database
check for valid values, not invalid ones
- try to build multiple lines of security
- be realistic about the threats you face - a power failure can be as
catastrophic as a hacker break-in.
- database design for performance
- database design for ease of maintenance
Any book by Date - try "Introduction to Database Systems". A good clean
normalised design pays dividends - don't denormalise without good arguments
why, and document the problems this will cause.
- triggers and functions
Check http://techdocs.postgresql.org/ - there are lots of articles/examples
there, especially in the cookbook.
Also, the application will require some complex forms, and printing
graphical reports (currently served from an Access 2000 database.) What
tools/interfaces/servers would you recommend for the application server?
I'd leave the reporting etc in Access for phase 1 if you can, and move it over
afterwards. If your users are on Windows, I'd probably not change at all. If
they're happy with the current reporting leave well alone.

Assuming you're going web-based, you might want to consider java, perl, php or
python frameworks. Look around and see if there isn't a Zope or perl/php app
that is close to what you need, perhaps.
Is there a tool you would recommend for generating graphical reports to
html?

Plenty of perl/php libraries for this. Presumably for python too.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html