Room中@Embedded用于客户与经销商关联场景是否合理？

Great question! Let’s break this down based on Room best practices and your specific business scenario: multiple customers linked to one reseller, with a single reseller serving many customers.

First, Why @Embedded Isn’t Ideal Here #

Your current setup uses @Embedded to nest CustomerResellerEntity inside CustomerEntity, which creates several issues:

• Data redundancy: Every customer record will duplicate the same reseller’s details (like name1). If a reseller’s information updates (e.g., their name changes), you’d have to update every customer row linked to them—this is inefficient and risks data inconsistency.
• Pointless primary key: Your CustomerResellerEntity has an auto-incrementing customerResellerId, but embedding this means every customer gets a unique, unrelated value for this field. Since resellers are shared entities, this adds no value and clutters your customer table.
• Violates database normalization: Flat embedding like this breaks third normal form, leading to long-term maintenance headaches as your dataset grows.

Why a Many-to-One Association Is the Better Fit #

Your business logic (many customers → one reseller) calls for a standard many-to-one relationship in Room. Here’s how to structure it properly:

Step 1: Separate Entities with Foreign Key

Split resellers into their own standalone entity, then link customers to them via a foreign key:

@Entity(tableName = "reseller")
data class ResellerEntity(
    @PrimaryKey val resellerId: String, // Use a unique ID for the reseller (from your API or generated)
    @ColumnInfo(name = "Reseller_name1") val name1: String?,
    // Add other reseller fields here...
)

@Entity(
    tableName = "customer",
    foreignKeys = [
        ForeignKey(
            entity = ResellerEntity::class,
            parentColumns = ["resellerId"],
            childColumns = ["reseller_id"],
            onDelete = ForeignKey.SET_NULL // Adjust delete behavior to match your needs
        )
    ]
)
data class CustomerEntity(
    @ColumnInfo(name = "Customer_id") @PrimaryKey override val id: String,
    @ColumnInfo(name = "Customer_name1") override val name1: String,
    // Add other customer fields here...
    @ColumnInfo(name = "reseller_id") val resellerId: String? // Foreign key linking to reseller
)

Step 2: Map API DTOs to Entities

Your API returns nested DTOs, but you don’t have to mirror that structure in Room. When parsing the API response:

1. Extract the CustomerResellerDto data, upsert it into the reseller table (create if new, update if existing).
2. Assign the reseller’s resellerId to the corresponding CustomerEntity before inserting into the customer table.

Step 3: Query Linked Data

To fetch customers along with their associated resellers, use Room’s @Relation and @Transaction for clean, atomic queries:

// Data class to hold joined customer + reseller data
data class CustomerWithReseller(
    @Embedded val customer: CustomerEntity,
    @Relation(
        parentColumn = "reseller_id",
        entityColumn = "resellerId"
    )
    val reseller: ResellerEntity?
)

@Dao
interface CustomerDao {
    @Transaction
    @Query("SELECT * FROM customer")
    suspend fun getCustomersWithResellers(): List<CustomerWithReseller>
}

Final Takeaway #

Using @Embedded here is indeed inappropriate for your business scenario. A many-to-one association aligns with database design best practices, eliminates data redundancy, and makes updates to reseller data far simpler and more consistent.

内容的提问来源于stack exchange，提问作者David