Android Room: Creating a one-to-one relationship entity

Question

I'm creating a small project with Room and three tables (jobs, tags, categories).

Essentially:

• A job belongs to (or has) 1 and only category, but a job can have 0..N tags.
• A category has 0..N jobs.
• A tag has 0..N jobs.

I'm having some trouble trying to model all the data, especially when it comes to the relationships between the entities. More concretely, I have:

fun JobListing.toJobEntityList(): List<JobEntity> {
    val jobEntityList = mutableListOf<JobEntity>()
    this.jobs.take(50).forEach { job: Job ->
        jobEntityList.add(
            JobEntity(
                jobId = job.id,
                title = job.title,
                url = job.url,
                companyName = job.companyName,
                jobType = job.jobType,
                publicationDate = job.publicationDate,
                relocation = job.candidateRequiredLocation,
                salary = job.salary
            )
        )
    }
    return jobEntityList
}

This extension function is being called when I'm fetching the data from the network, so I can convert it to entities and store them in my DB. I'm essentially creating a JobEntity, but a job should have 1 category and 0..N tags associated. The problem is that I don't know how to add that data related to the relationship between a job and its category and tags.

This is my JobEntity class:

@Entity(
    tableName = "Jobs",
    indices = [
        Index("id", unique = true)
    ]
)
data class JobEntity(
    @PrimaryKey @ColumnInfo(name = "id") val jobId: Int,
    @ColumnInfo(name = "title") val title: String,
    @ColumnInfo(name = "url") val url: String,
    @ColumnInfo(name = "company_name") val companyName: String,
    @ColumnInfo(name = "job_type") val jobType: String,
    @ColumnInfo(name = "publication_date") val publicationDate: String,
    @ColumnInfo(name = "candidate_required_location") val relocation: String,
    @ColumnInfo(name = "salary") val salary: String
)

Thanks in advance!

Answer 1

Hi you should model your database like this:

1. Job_Category
2. Tags
3. Jobs (add category_id field and add 1 foreign key referencing to Category table)
4. Job_Tags (add tag_id, job_id fields and add 2 foreign keys referencing to Category and Jobs tables)

@Entity(tableName = "Category")
data class CategoryEntity(
    @PrimaryKey @ColumnInfo(name = "id") val id: Int,
)

@Entity(tableName = "Tags")
data class TagEntity(
    @PrimaryKey @ColumnInfo(name = "id") val id: Int,
)

@Entity(
    tableName = "Jobs",
    foreignKeys = [ForeignKey(entity = CategoryEntity::class, parentColumns = ["id"], childColumns = ["categoryid"])]
)
data class JobEntity(
    @PrimaryKey @ColumnInfo(name = "id") val id: Int,
    @ColumnInfo(name = "categoryid", index = true) val categoryid: Int,
    // ... other fields
)
@Entity(
    tableName = "JobTags",
    foreignKeys = [
        ForeignKey(entity = TagEntity::class, parentColumns = ["id"], childColumns = ["tagId"]),
        ForeignKey(entity = JobEntity::class, parentColumns = ["id"], childColumns = ["jobId"]),
    ]
)
data class JobTagEntity(
    @PrimaryKey @ColumnInfo(name = "id") val id: Int,
    @ColumnInfo(name = "tagId", index = true) val tagId: Int,
    @ColumnInfo(name = "jobId", index = true) val jobId: Int,
)