Is this the expected way to handle it?

No. It is a wrong approach. You should eliminate duplication of data for several reasons.

• It will cost storage space. As the size of database increases, thiswill began to become worse.

• It will make the system complicated. If you are updating a singlefield, You may have to perform the same action at different places.If you miss any one of them, the entire data will becomeinconsistent. It is possible to perform such operations asa single transaction. But it is always better to keep the database structure clean.

Solution

Method 1: Introduce new tables

You can store details of books in only one table say "Books". "Books_To_Read" or any other table should contain only the reference to the "Books" table (By using the id/ primary key in "Books" table). You can then use the JOIN keyword to get the entire record at a single query.

This method is preferred if each type (read and unread books in this case) has it's own set of fields (Like the read_date, read_time for read books and wish_to_read for unread books).

Method 2: Introduce new field

You can simply add a new type which specifies the type. In this case, there are only two type(read and unread), a boolean field (something like is_read) will be fine.

This would be the easiest method. But this will work only if you just want to indicate which type the row belongs to. If you do need to store type specific data and you introduce additional fields for that purpose, remember that those fields will exists for others types too.

This is how I eventually solved this problem:

I added an ID to the Book.java class that represents whether it has been read or not. Backed by an EnumType that was either book_read or book_unread and then in my DAO for "BooksDataTable" I then just use a SQL query to select the ID I want. So by changing the query I can get either all the books_read or books_to_read or all_books. This way, there is no duplication of data and everything is in one table, without having to use JOIN or OneToMany relationships.

I was writing sample app when Ahamad gave his answer.

Room provides an abstraction layer over SQLite to allow fluent database access while harnessing the full power of SQLite.

main idea is same as of to store Book data in one table and use the id of the book in other tables. have a look at my sample project

result of logcat

02-19 16:39:26.741 10520-10520/com.example.jingged.roomtest E/MainActivity: Book{id = 1 name = Book 0, author Author 0}02-19 16:39:26.741 10520-10520/com.example.jingged.roomtest E/MainActivity: Book{id = 2 name = Book 1, author Author 1}02-19 16:39:26.741 10520-10520/com.example.jingged.roomtest E/MainActivity: Book{id = 3 name = Book 2, author Author 2}02-19 16:39:26.741 10520-10520/com.example.jingged.roomtest E/MainActivity: Book{id = 4 name = Book 3, author Author 3}02-19 16:39:26.767 10520-10520/com.example.jingged.roomtest E/MainActivity: BookToRead Book{id = 3 name = Book 2, author Author 2}02-19 16:39:26.767 10520-10520/com.example.jingged.roomtest E/MainActivity: BookToRead Book{id = 4 name = Book 3, author Author 3}02-19 16:39:26.768 10520-10520/com.example.jingged.roomtest E/MainActivity: BooksRead Book{id = 1 name = Book 0, author Author 0}02-19 16:39:26.768 10520-10520/com.example.jingged.roomtest E/MainActivity: BooksRead Book{id = 2 name = Book 1, author Author 1}