Room: Database Relationships

Most modern applications today use databases for offline storage. Lucky for us, this interaction is quite easy using Room Persistence Library.In this tutorial, we’ll learn how to work with multiple tables that have relationships with each other. First, we will go over some core concepts, and then will begin working with JOIN queries in SQL.

Introduction

When we creating a database, we separate tables for different types of entities. For examples, customers, orders, items etc… But we also need to have relationships between these tables. For instance, customers make orders, and orders contain items. These relationships need to be represented in the database. Also, when fetching data with SQL, we need to use certain types of JOIN queries to get what we need.

There are several types of database relationships. Today we are going to cover the following:

  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships

When selecting data from multiple tables with relationships, we will be using the JOIN query.

Room: One-To-One Relationships


In this example you will learn how to map one-to-one relationship using Room. Consider the following relationship between Customer and Address entity.
Room: One-To-One mappingTo create this relationship you need to have a CUSTOMER and ADDRESS table. The relational model is shown below.

Room: One-To-One mapping

Use a primary key

Each entity must define at least 1 field as a primary key. Even when there is only 1 field, you still need to annotate the field with the @PrimaryKey annotation. Also, if you want Room to assign automatic IDs to entities, you can set the @PrimaryKey‘s autoGenerate property.

Define relationships between objects

You need to specify relationships between customer and address objects. The Room allows you to define Foreign Key constraints between entities.

For example, if there’s Customer entity, you can define its relationship to the Address entity using the @ForeignKeyannotation, as shown in the following code snippe

Now we have a relationship between the Customers table and the Addresses table. If each address can belong to only one customer, this relationship is “One to One”. Keep in mind that this kind of relationship is not very common. Our initial table that included the address along with the customer could have worked fine in most cases.
Notice that now there is a field named “address_id” in the Customers table, that refers to the matching record in the Address table.

Room: One-To-Many Relationships


This is the most commonly used type of relationship. Consider an e-commerce app, with the following:

  • Customers can make many orders
  • Orders can contain many items
  • Items can have descriptions in many languages

In these cases, we would need to create “One to Many” relationships. Here is an example:
In the following example, you will learn how to map one-to-many relationship using Room. Consider the following relationship between Customer and Order entity.
Room: One-To-ManyAccording to the relationship, a Customer can have any number of Orders.
To create this relationship you need to have a Customer and Order table. The relational model is shown below.
Room: One-To-Many

Each customer may have zero, one or multiple orders. But an order can belong to only one customer.

To create Order table you need to create the following Java bean class.

Room: Many to Many Relationships


In some cases, you may need multiple instances on both sides of the relationship. For example, each order can contain multiple items. And each item can also be in multiple orders.

Room: Many to Many RelationshipsFor these relationships, we need to create an extra table:
Room: Many to Many Relationships
The item_order table has only one purpose, and that is to create a “Many to Many” relationship between the items and the orders.

To create the items and item_order tables you need to create the following Java Bean class.

Annotate indices and uniqueness

Sometimes, certain fields or groups of fields in a database must be unique. You can enforce this uniqueness property by setting the unique property of an @Index annotation to true. The following code sample prevents a table from having two rows that contain the same set of values for the order_id and item_id columns:

Join Queries


Some of your queries might require join tables to calculate the result. Room allows you to write any query. Furthermore, if the response is an observable data type, such as Flowable or LiveData, Room watches all tables referenced in the query for invalidation.

The following code snippet shows how to perform a table join to consolidate information between a table.

 

Related Post

Room Persistence Library

How to use DateTime datatype in SQLite Using Room

Room database Migrating

 

Leave a Reply

Your email address will not be published. Required fields are marked *