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.
精彩评论