List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:February 16 2009 11:30pm
Subject:Re: how to deal with multiple authors for one book
View as plain text  
On Mon, Feb 16, 2009 at 5:20 PM, PJ <af.gourmet@stripped> wrote:
> In my db there are a number of books with several authors; so, I am
> wondering how to set up a table on books and authors to be able to
> insert (via php-mysql pages) data and retrieve and display these books
> with several authors
> I suspect that to insert data for a multiple author book I will have to
> enter all data other than the author names into the book table and enter
> the authors in the author tables with foreign keys to reference the
> authors and their book.
> Then to retrieve and display the book,I would have to use some kind of
> join instruction with a where clause(regarding the position - 1st, 2nd,
> 3rd...) to retrieve the authors and their order. The order would
> probably be done by a third field (e.g. f_name, l_name, position) in the
> book_author table (tables in db -  book, author, and book_author)
> Am I on the right track, here?
>

Close.

You have to consider that this is a many-to-many relationship books
may have many authors and authors may have many books.  This will
reuire the use of a linking table and it's there that I suggest you
need too maintain your ordinal field (I use 'ordinal' by convention as
'order' is a SQL keyword)

AUTHORS
 - authorid
 - name
 -- other fields

BOOKS
 - bookid
 - title
 -- other fields

A2B
 - bookid
 - authorid
 - ordinal


-- 
 - michael dykman
 - mdykman@stripped

 - All models are wrong.  Some models are useful.
Thread
how to deal with multiple authors for one bookPJ16 Feb
  • Re: how to deal with multiple authors for one bookMichael Dykman17 Feb
    • Re: how to deal with multiple authors for one bookPJ17 Feb
      • Re: how to deal with multiple authors for one bookMichael Dykman17 Feb
Re: how to deal with multiple authors for one bookPJ24 Feb
  • Re: [PHP] Re: how to deal with multiple authors for one bookBastien Koert25 Feb