Re: Function to Pivot data - Mailing list pgsql-general

On Thu, Jan 31, 2002 at 11:43:37AM -0500, Ellen Cyran wrote:
>
> A few of the tables in a normalized database would be:
>     Author:
>     AuthorID, LastName, FirstName, DepartmentID

>     Author_Book:
>     AuthorID, BookID


Seems to me like the author_book table will need a field which
indicates "first author", "second author", &c.  You can't just sort
alphabetically, because that might not be the correct precedence.  So
you need something like authorno (probably NOT NULL DEFAULT 1, but
you'll have to do some extra work to make sure that you never have a
book with more than one 1st author, 2d author, &c.).

>     Title, Date, Author1, Author2, Author3, Author4
>
> Where Author1, Author2, etc. are FirstName + LastName.

I'm not an expert in designing this sort of thing, and someone is
going to choke when s/he sees what a horribly inefficient way this
works (if I gave it more thought, I could probably come up with a
better answer).  Still, this would work in case you have a known
number of authors for every book:

SELECT a.title,b.firstname||' '||b.lastname AS auth1, c.firstname||'
'||c.lastname AS auth2, a.pubdate FROM books AS a, author AS b,
author AS c, author_books AS d, author_books AS e WHERE
a.id=d.book_id AND b.id=d.auth_id AND d.auth_rank=1 AND
c.id=e.auth_id AND e.auth_rank = 2;

I doubt you'll have that case, though, and you'd have to add some
LEFT JOINs to the mix.  For any amount of data at all, you'll have a
performance problem.

But I wonder if the difficulty might be because you're trying to
normalise a simple one-to-many relation, and you actually have a
one-to-many relation which has order in the "many" side.  That's a
different problem, really, and probably needs something like a
unified book-author table with the book information in it:

CREATE TABLE book     (title text,
             pubdate date,
             author1 int4,
             author2 int4,
             author3 int4 . . .

The trouble in this case is that you'll be limited to some maximim
number of authors.  (This is one problem the MARC cataloguing
standard gets around.  But I think that's rather more complicated
that you want.)

The author info could still be made separate, and references could be
put in the book table so that if the author's info changed, you could
change it for every book entry in one go.

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110