MySQL: put all values of 1:m into one field?

Question

Assume the following possible models:

Model 1:

TABLE: book
- book_id
- name

TABLE: book_author
- book_author_id
- book_id
- author_id

TABLE: author
- author_id
- name

(a book can have 0 or more authors)

Model 2:

TABLE: book
- book_id
- name

TABLE: book_eav
- book_eav_id
- book_id
- attribute (e.g. "author")
- value (e.g. "Tom Clancy")

(a book can have 0 or more authors + information about publisher, number of pages, etc.)

How do I query this in such a way that I get back book.book_id, book.name + an authors field that contains the 0 or more values associated with the book?

I'm trying to do this so I can use the query results as documents in a Lucene index.

Answer 1

Lucene supports multiple fields with the same name, meaning that you can actually store multiple fields named "Author" on the same document.

I suggest that you actually do that, and fetch additional data using another statement, for example:

one select for books. one select for book_id and the author.