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?

Answer 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.