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.
