Database relations: one-to-one, one-to-many, many-to-many

Database relations with Room

Florina Muntenescu
Nov 21, 2019 · 5 min read

An important part of designing a relational database is splitting the data into related tables and pulling the data together in meaningful ways. Starting with Room 2.2 (now stable) we have support for all possible relations between tables: one-to-one, one-to-many and many-to-many, with one annotation: @Relation.

One-to-one relations

One-to-one relations

Let’s say that we live in a (sad) world where a person can own only one dog and a dog can have only one owner. This is a one-to-one relation. To model this in a relational database, we create two tables: Dog and Owner, where the Dog table has a reference to the owner id, or the Owner has a reference to a dog id. In Room, we create two entities:

data class Owner(@PrimaryKey val ownerId: Long, val name: String)

Let’s say that we want to display the list of all dogs and their owners on the screen. To do this, we would create a DogAndOwner data class:

To query this using SQLite, we would need to 1) run two queries: one that gets all owners, and one that gets all dogs based on owner ids and then 2) handle the object mapping.

WHERE dogOwnerId IN (ownerId1, ownerId2, …)

To get a List<DogAndOwner> using Room, we don’t need to implement the two queries ourselves and handle the object mapping, but rather, use the @Relation annotation.

In our example, since Dog has the owner’s information, we add the @Relation annotation to the dog variable,: specifying that the ownerId column on the parent (i.e. the Owner entity) corresponds to the dogOwnerId:

Our Dao is now simplified to:

Note: Because Room runs the two queries for us under the hood, add the @Transaction annotation, to ensure that this happens atomically.

One-to-many relations

One-to-many relations

Let’s say that an owner can have multiple dogs (yay!); we’d have a one-to-many relation between Dog and Owner. The database schema we previously defined doesn’t change — we still have the same tables, since the relating key is already in the “many” table of the relationship.

Now, to display the list of owners with their dogs, we need to create a new data class to model this:

To avoid running two separate queries, we can define a one-to-many relation between Dog and Owner, by annotating the List<Dog> with @Relation as before:

The Dao becomes:

Many-to-many relations

Many-to-many relations

Now suppose we live in a perfect world where an owner can have multiple dogs, and that a dog can have multiple owners. To model this schema, our Dog and Owner tables are not enough. Since a dog can have multiple owners, we need to have multiple entries of the same dog id, matching to different owner ids. Because dogId is the primary key in Dog, we can’t insert multiple dogs with the same id. To overcome this, we need to create an associative table (also known as cross-reference table) that keeps (dogId,ownerId) pairs:

If we now want to get the list of all owners with dogs: List<OwnerWithDogs>, using just SQLite queries, we need to write two queries: one that gets all owners and one that joins the Dog and the DogOwnerCrossRef tables:

Dog.dogId AS dogId,
Dog.dogOwnerId AS dogOwnerId, AS name,
DogOwnerCrossRef AS _junction
INNER JOIN Dog ON (_junction.dogId = Dog.dogId)
WHERE _junction.ownerId IN (ownerId1, ownerId2, …)

To implement this in Room, we need to update our OwnerWithDogs data class and tell Room that in order to get the Dogs, it needs to use the DogOwnerCrossRef associate table. We reference the table by using a Junction:

In our Dao, we need to select from Owners and return the right data class:

Advanced relation use cases

When using the @Relation annotation, Room infers the entity to use from the type of the annotated property by default. For example, until now we annotated a Dog (or a List<Dog>) with @Relation, telling Room how to model the class and which columns to query

If we want to return a different object, for example a Pup, that is not an entity but contains some of the fields, we can specify the entity to use in the @Relation annotation:

data class OwnerWithPups(
@Embedded val owner: Owner,
parentColumn = "ownerId",
entity = Dog::class,
entityColumn = "dogOwnerId"
val dogs: List<Pup>

If we want to return only specific columns from an entity you need to tell Room which these are by defining them in the projection property of the @Relation. For example, let’s say that we just want to get the names of all the dogs in our OwnerWithDogs data class. Since we would need a List<String>, Room can’t deduce whether those strings correspond to the name or to the breed, so we need to specify the column in the projection:

If you want to define a stricter relationship between the dogOwnerId and ownerId, independent of what kind of relation you’re creating, use a ForeignKey constraint between the fields. Keep in mind that SQLite foreign keys define indices and can have cascading triggers that update or delete entries in your tables. So decide whether you want to use foreign keys based on whether you do want this kind of functionality in your database.

Whether you need one-to-one, one-to-many or many-to-many support, Room has you (and your doggos) covered with one annotation: @Relation. Find out more about Room 2.2 features from our Android Dev Summit ’19 talk:

Android Developers

The official Android Developers publication on Medium

Thanks to Manuel Vivo and Nick Butcher

Florina Muntenescu

Written by

Android Developer Advocate @Google

Android Developers

The official Android Developers publication on Medium

More From Medium

More from Android Developers

More from Android Developers

More from Android Developers

Layout Inspector

More from Android Developers

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade