Exploring One-to-One Relationship Models in Room Database

Room Database is a persistence library provided by Google and acts as an abstraction layer over the underlying SQLite database.

This means Room simplifies database setup and configuration allowing your app to interact with the database through ordinary function calls.

One fundamental concept in database design that Room Database handles exceptionally well is the one-to-one relationship.

This relationship type defines a scenario where each row in one table is associated with exactly one row in another table.

One-to-One tables relation

On the above illustration, each row from table one is mapped to one row of table two. This concept can be visualized as a single table split halfway with a common property.

Classic Examples

Some examples of one-to-one relationship modeling include:

• Country and Capital: each country has a unique capital, and each capital is associated with exactly one country
• User and UserProfile: user’s username and password data can be stored in one table while additional profile information (like email, bio, etc.) could be stored in a separate table linked to the user table.
• Employee and EmployeeInfo: HR database system can have employee’s basic details like Id, name, etc in one table with a supplementary table holding additional info such as contact details, home address etc.
• Customer and CreditCard: In a banking system each customer might have one credit card associated with their account.

One-to-One Relationship Model in Code

This is how the above tables would look like in code.

@Entity(tableName = "countries_table")
data class Country(@PrimaryKey val id: Long, val name: String, val lang: String)

@Entity(
        tableName = "capitals_table",
        foreignKeys = [
            ForeignKey(
                    entity = Country::class,
                    parentColumns = arrayOf("id"),
                    childColumns = arrayOf("country_id"),
                    onDelete = ForeignKey.CASCADE
            )
        ]
)

data class Capital(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo("id")
    val id: Long,
    @ColumnInfo(name = "country_id")
    val countryId: Long,
    @ColumnInfo(name = "name")
    val name: String
)

You will notice that Capital entity specifies a foreign key to link it to the Country Entity.

The foreign key column will act as a common column and will be used on the POJO class to specify the relationship between Country and Capital.

You can find more info on foreign keys on this blog.

Connecting Room Tables Using Foreign Keys

Intemediary Class

When querying the database the dao will return neither Country nor the Capital entities.

This is where an intermediary Plain Old Java Object (POJO) comes in. We need a POJO helper class which models one-to-one relationship between two entities.

data class CountryAndCapital(
    @Embedded val country: Country,
    @Relation(
            parentColumn = "id",
            entityColumn = "country_id"
    )
    val capital: Capital
)

This POJO tells Room how our entities relate and work together.

• The name of the POJO is CountryAndCapital which is a convention to combine first and second class names using With or And conjunctions
• In the POJO one entity must include a reference to the primary key of the other entity
• @Embedded annotation applied to the country property tells Room to include all properties of the Country class as columns in the CountryWithCapital table. This means that properties like id, name, and lang from the Country class will internally be treated as columns in the database table for CountryWithCapital
• @Relation is an annotation used in a POJO to specify the relationship between Country and Capital and automatically fetch relation entities
• parentColumn refers to the column in the parent entity - Country
• entityColumn refers to the column in the child entity - Capital

Querying a One-to-One Relationship Model

We can then query all users with their addresses like this:

@Dao
interface CountryCapitalDao {
    @Query("SELECT * FROM countries")
    fun getCountriesWithCapitals(): List<CountryWithCapital>?
}

This query will return a list of CountryAndCapital objects, each containing a Country object and a Capital object which you can then employ on your project’s logic.

Happy Coding