parsing and storing ebook content, sqlite indexing

Question

I am looking to store books and have the ability to search individual pages. It will only search a page at a time.

My initial thoughts have been to store books in a database by page number and a column revealing what chapter/section the page is in

so the columns would be

id

book id

book name

text

page

chapter

chapter name

text would be the largest portion

I will be using the android OS, so given what is available that would this be a good solution/sorting paradigm? there is a similar question about storing books not specific to android, and the consenses was to use other technologies

Answer 1

I think this is good a solution as any, for presenting pages to the user. You can add indexing to book id and page, as users will request specific pages to read (bookmarks, next page, previous page, first page if starting a new book). You will have 1-2 requests per minute so even without indexing it won't take too long, you can precache the next page in advance to make the experience even smoother.

While flat database structure is a good solution when performance is an issue, I would strongly suggest three tables:

BOOK (book_id,book_title,book_author,book_ISBN,book_release_date,...)

CHAPTER (chapter_id,chapter_number,chapter_title,book_id_ref)

PAGE (page_id,page_number,chapter_id_ref)

This way any modifications in the meta-data can be done at one place, you don't have to alter every page to modify a book's author. Also if you extend the meta data with any new fields, you have to change only the book table's records.

If on the other hand you would like to implement text search, than this won't work. For that you would have to build a word database, each word having it's occurrences listed. This will more than double your database size.