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

@Entity
data class Dog(
@PrimaryKey val dogId: Long,
val dogOwnerId: Long,
val name: String,
val cuteness: Int,
val barkVolume: Int,
val breed: String
)
@Entity
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:

data class DogAndOwner(
val owner: Owner,
val dog: Dog
)

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.

SELECT * FROM OwnerSELECT * FROM Dog
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:

data class DogAndOwner(
@Embedded val owner: Owner,
@Relation(
parentColumn = "ownerId",
entityColumn = "dogOwnerId"
)
val dog: Dog
)

Our Dao is now simplified to:

@Transaction
@Query("SELECT * FROM Owner")
fun getDogsAndOwners(): List<DogAndOwner>

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

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

data class OwnerWithDogs(
val owner: Owner,
val dogs: List<Dog>
)

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:

data class OwnerWithDogs(
@Embedded val owner: Owner,
@Relation(
parentColumn = "ownerId",
entityColumn = "dogOwnerId"
)
val dogs: List<Dog>
)

The Dao becomes:

@Transaction
@Query("SELECT * FROM Owner")
fun getDogsAndOwners(): List<OwnerWithDogs>
Many-to-many relations

Many-to-many relations

@Entity(primaryKeys = ["dogId", "ownerId"])
data class DogOwnerCrossRef(
val dogId: Long,
val ownerId: Long
)

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:

SELECT * FROM OwnerSELECT
Dog.dogId AS dogId,
Dog.dogOwnerId AS dogOwnerId,
Dog.name AS name,
_junction.ownerId
FROM
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:

data class OwnerWithDogs(
@Embedded val owner: Owner,
@Relation(
parentColumn = "ownerId",
entityColumn = "dogId",
associateBy = Junction(DogOwnerCrossRef::class)
)
val dogs: List<Dog>
)

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

@Transaction
@Query("SELECT * FROM Owner")
fun getOwnersWithDogs(): List<OwnerWithDogs>

Advanced relation use cases

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 Pup(
val name: String,
val cuteness: Int = 11
)
data class OwnerWithPups(
@Embedded val owner: Owner,
@Relation(
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:

data class OwnerWithDogs(
@Embedded val owner: Owner,
@Relation(
parentColumn = "ownerId",
entity = Dog::class,
entityColumn = "dogOwnerId",
projection = ["name"]
)
val dogNames: List<String>
)

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

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