I am designing an Android app with a fairly complex (normalized) database structure. My question is, what is the best design pattern for working with my data?
The database has a table Books, a table Authors, a table Collections (of books), and a junction table Books_Collections that relates the two based on an integer ID. I say that it is fairly complex because I want to display a list of books & authors in a specific a collection in a ListView in format "Book Title (Author Name)", so I need to be able to perform the following query (I will abbreviate the table names A, B, C, and B_C):
SELECT B.title, A.name
FROM B
JOIN A ON B.author_id = A.id
JOIN B_C ON B_C.book_id = B.id
WHERE B_C.collection_id = [variable]
I have seen some people advising developers to wrap their Sqlite databases in a Content Provider to easily take advantage of the Loaders introduced in Honeycomb. But is this really feasible for such complex queries? (If so, how?) or would it be better to just use DAO since I am not sharing my data with other apps? And if using DAO, should a custom AsyncTaskLoader be created to connect the data to a ListView?
EDIT One more important detail: I want to change the background color of the list items based on a boolean variable in Books marking whether or not the book has been read, and a context menu will allow users to delete books from the list, so the list needs to adjust instantly to data changes.
I ended up using database helpers/adapters and creating a custom AsyncTaskLoader. I wasn't able to find a ton of examples of custom ones, and there's not great documentation on it, but it's not too hard to figure out. Works like a charm.