Michael Dykman wrote: > 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 What do you mean by "ordinal" is this a primary key???
Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
