On Mon, Feb 16, 2009 at 5:20 PM, PJ <af.gour...@videotron.ca> 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 - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
