Audiobook tracking solution
I want to come up with simple audiobook tracking solution, where i could input tracks i've listened in the particular date, and then i could summarize and total either books i've listened with dates and/or audiobook count.
As I imagine, core table should be AUTHOR (as author can have many books written) (parent field - AUTHORID)
then - BOOK (title, year, genre, parent field - BOOKID (auto-enter serial number))
then, I believe, it would be right to have table TRACKS, and therefore I could link track titles and their length to AUDIOBOOK using BOOKID field.
I need only those features:
1) Ability to view books of the particular author (related records from BOOK, and then related to TRACKS, so i could see total length of the book in hours:minutes). I believe I'm not going to have a problem doing it myself.
2) "Tricky part" - "Add listened track" feature, where i could input: Author, Book, Track, Date listened. the "trick" in this part lies in the following, that since the book is not fully listened, it would be marked as not completed. I mean. Book consists of 8 tracks. I listened 3 tracks today and 2 tracks yesterday. 5/8 completed. In the total feature it should list a track, but when it comes to sub-summary field on the top "Books per week" - it would display zero until it's fully completed.
My concerns lay mostly on the layout. It can either be like this:
1 Author - Book title - 58:47
1 Author - Book title - Track 1 - 41:10
2 Author - Book title - Track 2 - 17:37
I believe my explanation is quite complex, but the irony of that is I want as simple as possible solution, being able to list all the tracks i've listened in any particular date/week/month and have totals.
As most of you are probably more experienced than me in database solution creation, please, tell me the most convenient way of achieving this.