From: Michael Dykman
Date: February 16 2009 11:30pm
Subject: Re: how to deal with multiple authors for one book
List-Archive: http://lists.mysql.com/mysql/216357
Message-Id: <814b9a820902161530u6c18d597rca52801ab10650c6@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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.