Multiple tables with same type of objects in Room database

Question

I'm using Room as the database for the app. I have a scenario where an Object of a certain type needs to be stored in separate tables. As an example, lets take the Object called Book.java

Now, I want to have two SQL tables:

• Books_Read
• Books_To_Read

ignore any naming conventions for SQL DB please - this is just an example

Problem

Normally, one would just use @Entity(tableName = "Books_Read") in the Book.java class and have a DAO class that will use that table name.

The thing is; how would I then be able to use the same Book.java class to store in the Books_To_Read table? Since I already defined @Entity(tableName = "Books_Read") as part of the Book.java class and I see no where to define the Books_To_Read table for the Book.java class

The only solution I was able to come up with, which seems a little hackery and unnessasery, was to create a new class - let's call it BookToRead.java that extends Book.java and define @Entity(tableName = "Books_To_Read") in the class.

Question

Is there a better way to do this or is this the expected way to handle it?

Solution 1

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, this will began to become worse.

• It will make the system complicated. If you are updating a single field, You may have to perform the same action at different places. If you miss any one of them, the entire data will become inconsistent. It is possible to perform such operations as a 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.

Solution 2

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.

Solution 3

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}