Re: Single VIEW, and FUNCTION questions...

Wyatt,

> I've got a book database here, and I'm trying to create a single VIEW
> that lists *all* of the books.  Here are the relavent parts:

If you lived with my fiancee, you'd give up on the idea of listing all
the books ;-}

> What I am trying to do is get a single VIEW to include *all* of the
> books, as well as the series, place in the series, and set *if* they
> have them.  But, since not all books belong to a series or set, all I
> have been able to manage is to get several VIEWs: one with all of the
> books in a series, one with books in a set, one with books in both
> series *and* sets, and one with the others --- with some overlap
> (books in sets *and* series, for instance, end up in all three of the
> VIEWs).

Depending on how your relationships work, you either want a UNION query
ro to replace a bunch of your JOINS with LEFT OUTER JOINS.  How about
posting the view definitions so we can offer informed suggestions?

Alternately, any good SQL book should be able to explain how to use a
UNION statement, now that you know what to look up ...

> Also, I'm trying to figure out how much should be implemented as
> FUNCTIONS in the database, and how much should be in the Java/Web
> portion.  For instance, when a book is deleted out of the book table,
> should a I delete all of the references to that book in the database
> with a trigger, or do it with the Java portion?

That's quite a YMMV question, depending on a variety of factors:
1. Are you using a real middleware layer (J2EE, Corba, Enhydra, etc.) or
just client-side Java code and/or JSPs?
2. What sorts of business rules will you want to enforce?  Simple or
complicated?
3. Are you more comfortable with Java or with SQL and PL/pgSQL?
4. Will some users be able to query the database directly?

Basically you're gonna want to devise the full set of business rules on
paper before you write a line of code.  Then you can look at the rules
and try to decide the most practical way to code them.  Two simple rules
that have worked for me:
1. You want to keep your business rules in a single application layer.
Thus, if you're enforcing complicated rules using Java middleware, you
want to keep your database constraints and triggers very basic, lest you
trip yourself up.
2. You never, ever, want to have business rules other than simple data
entry validation in the client-side code.  That is the route to
debugging hell ...

Beyond that, there are several books about application design and good
coding practices.  

-Josh Berkus

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