Need some advice to maintain a certain order within datasets within MySQL

Assume a list of names in a table called authors. Each author has written some books during his lifetime. Important: You don't know the explicit dates when the books were published. (If I would now the exact dates, I could just order by that in each query and I would be good.) You only know the order of the books' publication. The books are stored in a table books. We have a Many-To-Many-Relation between authors and books.

The question: How would you store the order of a publication history of each of the authors?

Example:

Luc - title_1
Luc - title_2
Luc - title_10
Luc - title_234

Peter - title_1
Peter - title_5
Peter - title_10
Peter - title_987

John - title_2
John - title_5
John - title_9

...

At 开发者_开发问答the moment I am storing for each author a string of comma separated Values (primary keys of the books) to remember the order of the publications of a certain author in a column called books_order.

id    name      publication_order
...
4     john      (2, 5, 9)
...

But that is certainly the wrong way to go. How to normalize that?

I would add an extra column called sortorder or somethin similar. The first book gets a 1, the second a 2 ... etc. With that, you can use ORDER BY author, sortorder to get a list of books sorted by author and order of publication.