Обсуждение: Single VIEW, and FUNCTION questions...

Hey all,

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
relaventparts:
 

Data tables: book, author, publisher, category, series, set
These tables store the appropriate data for each table; for instance, the book table stores the ISBN (if any), title,
publisher(index to publisher table), copyright year, and a primary key series.
 

Joining tables: bookauthor, bookcategory, bookseries, bookset
These tables are generally columns containing integers for each of the keys to join them; the bookauthor has a book key
andan author key, the bookseries has a book key, series key, and the place in the series (book 1, 2, etc).
 
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,
andset *if* they have them.  But, since not all books belong to a series or set, all I have been able to manage is to
getseveral 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
theVIEWs).
 

Is there a way to get all of these into a single VIEW with a single query?  Or am I going to have to do several queries
fromJava or Perl and put them together (final project is in Java)?
 

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

Thanks,
Wyatt

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
 

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