Re: Single VIEW, and FUNCTION questions...

Okay.  Tables are as follows (Note, for lack knowledge of a better practice, fields such as ibn and icn stand for
'IncrementalBook Number', 'Incremental Category Number', etc.  Also, where these were listed in the table as integers
withall of the modifications, I changed them to serial here.):
 

book:
-----------+---------------+ibn       | serial        |isbn      | character(10) | title     | text          |
publisher| integer       |  From publisher tablecyear     | integer       |  Copyright Year
 
Index: book_ibn_key

author:
-----------+---------+ian       | serial  |last      | text    | first     | text    | middle    | text    | 

bookauthor:
-----------+---------+ian       | integer | ibn       | integer | 

series:
-----------+---------+isn       | serial  |name      | text    | count     | integer |  Total number of books in
seriescomplete | boolean | 
 

bookseries:
-----------+---------+isn       | integer | ibn       | integer | place     | integer |  Which book in the series (1,
2,etc.)
 

The publisher, category and set tables look much like the author table: with an incremental primary key and a name
field. The bookcategory and bookset tables look like the bookauthor table, with just the two keys from the other
tables.

So anyway, some books are parts of sets, some are part of series', and some are single books.  If possible, I'd like a
singleVIEW to show book and author info, and if it applies, series and/or set info as well, with those fields blank for
singlebooks.  And it's going to need a helluva long ORDER BY statement, if that's possible (if I'm thinking right, it
wouldbe: set.name, author.last, author.first, bookseries.name, bookseries.place, and finaly book.title; I don't know
whatto do about multi-authored series' though...)
 

Thanks,
Wyatt

And I've got 330 books in at the moment, and I haven't touched my computer book library yet.  If I ever see another
ISBNin my life... Heh.
 

> 
> 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 information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco