45

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?

Bertram Gilfoyle
  • 9,899
  • 6
  • 42
  • 67
Robert J. Clegg
  • 7,231
  • 9
  • 47
  • 99
  • 4
    I mean if it's the same entity - why do you want to have different tables for this then? Imo it should be the same table but just with additional column like "to_read" or whatever. – romtsn Feb 13 '18 at 20:13
  • @rom4ek You're right. I realised this soon after posting this question. I eventually solved it by having one table and a new column added to differentiate between he different types. – Robert J. Clegg Feb 14 '18 at 07:15
  • I am not sure if this is only related to this particular case, but having an extra field / column to the table telling if the book was read or not, won't do the job? Why you want them stored in different tables ? – Ionut J. Bejan Feb 20 '18 at 07:10
  • i have a similar issue. data can become stale after 24 hours so i delete all the saved data after this time. therefore, ill delete weather i have favourited the object on delete (deleting boolean varibale stored in the obj). therefore i potentially need another table and or flag in shared prefs. currently using room aswell. – filthy_wizard Aug 07 '18 at 17:44

3 Answers3

40

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.

Bertram Gilfoyle
  • 9,899
  • 6
  • 42
  • 67
  • I understood and liked method 2 very much. But I'm having trouble understanding method 1. Can you explain it a bit more with an example or link me to a suitable documentation? – Cyber Avater Sep 08 '22 at 19:42
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.

Robert J. Clegg
  • 7,231
  • 9
  • 47
  • 99
1

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}
Sahil Manchanda
  • 9,812
  • 4
  • 39
  • 89