On Mon, Feb 16, 2009 at 8:37 PM, PJ <af.gour...@videotron.ca> wrote: > 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??? >
ordinal is just the sorting key you use to define what order to present the many authors of a book. I use 'ordinal' generically whenever I need an arbitrary number to use for pure ordering purposes. -- - 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
