ROOM database entity modeling

There must be a better way of doing this. I want to create a database table with all my clothing and have subcategories of clothing, like outerwear, dresses, shoes, etc. They all will have the same attributes (Id, name, image, about, price). Couldn't I create one table? I believe this is a One-to-Many relationship.

@Serializable
@Entity(tableName = CLOTHING_DATABASE_TABLE)
data class Clothing(
    @PrimaryKey(autoGenerate = false)
    val id: Int,
    val name: String,
    val image: String,
    val about: String,
    val price: String
)

@Serializable
@Entity(tableName = POPULAR_DATABASE_TABLE)
data class Popular(
    @PrimaryKey(autoGenerate = false)
    val popularId: Int,
    val name: String,
    val image: String,
    val about: String,
    val price: String
)

@Serializable
@Entity(tableName = OUTERWEAR_DATABASE_TABLE)
data class Outerwear(
    @PrimaryKey(autoGenerate = false)
    val outerwearId: Int,
    val name: String,
    val image: String,
    val about: String,
    val price: String
)

@Serializable
@Entity(tableName = TOPS_DATABASE_TABLE)
data class Tops(
    @PrimaryKey(autoGenerate = false)
    val topsId: Int,
    val name: String,
    val image: String,
    val about: String,
    val price: String
)

@Serializable
@Entity(tableName = SWIMWEAR_DATABASE_TABLE)
data class Swimwear(
    @PrimaryKey(autoGenerate = false)
    val swimwearId: Int,
    val name: String,
    val image: String,
    val about: String,
    val price: String
)

@Serializable
@Entity(tableName = SHOES_DATABASE_TABLE)
data class Shoes(
    @PrimaryKey(autoGenerate = false)
    val shoesId: Int,
    val name: String,
    val image: String,
    val about: String,
    val price: String
)

@Serializable
@Entity(tableName = BUNDLES_DATABASE_TABLE)
data class Bundles(
    @PrimaryKey(autoGenerate = false)
    val bundlesId: Int,
    val name: String,
    val image: String,
    val about: String,
    val price: String
)

@Serializable
@Entity(tableName = DRESSES_DATABASE_TABLE)
data class Dresses(
    @PrimaryKey(autoGenerate = false)
    val dressesId: Int,
    val name: String,
    val image: String,
    val about: String,
    val price: String
)

@Serializable
@Entity(tableName = PAJAMAS_DATABASE_TABLE)
data class Pajamas(
    @PrimaryKey(autoGenerate = false)
    val pajamasId: Int,
    val name: String,
    val image: String,
    val about: String,
    val price: String
)

@Serializable
@Entity(tableName = ACCESSORIES_DATABASE_TABLE)
data class Accessories(
    @PrimaryKey(autoGenerate = false)
    val accessoriesId: Int,
    val name: String,
    val image: String,
    val about: String,
    val price: String
)

Solution

You would typically have either 2 or 3 tables (3 for a many-many i.e. an item of clothing could have multiple sub-categories).

For one-many have a clothing table which has a column for the sub-category that references(relates) to the single sub-category and a sub-category table that is referenced according to a unique column (the primary key).

For the many-many you have the clothing table (without the column to reference the single sub-category), the sub-category table and then a third table that has two columns, one for the reference to the clothing and the other for the reference to the sub-category with the primary key being a composite of both.

So you could have:-

@Entity(tableName = CLOTHING_DATABASE_TABLE)
data class Clothing(
    @PrimaryKey(autoGenerate = false)
    val id: Long, /* should really be Long as */
    val subCategoryReference: Long, /*<<<<< ADDED for the 1 subcategory */
    val name: String,
    val image: String,
    val about: String,
    val price: String
)

and :-

@Entity(tableName = SUBCATEGORY_DATABASE_TABLE)
data class SubCategory(
    @PrimaryKey
    val id: Long?,
    val subCategoryName: String
)

to enforce referential integrity you could add a foreign key constraint to the subCategoryReference column of the clothing table.

If you wanted a many-many, allowing a clothing to have multiple sub-categories then you could have the third table as :-

@Entity(
    tableName = CLOTHING_SUBCATEGORY_MAP_DATABASE_TABLE,
    primaryKeys = ["clothingMap","subcategoryMap"],
)
data class ClothingSubCategoryMap(
    val clothingMap: Long,
    @ColumnInfo(index = true)
    val subcategoryMap: Long
)

Of course you could have a single clothing table and just have a column for the sub-category. However this would be considered as not being normalised as you would be duplicating the sub-category throughout.

Example 1-many (i.e. using the 2 tables Clothing and SubCategory)

As you would very likely want to retrieve clothing along with it's sub-category then you would have a POJO that uses the @Embedded and @Relation annotations e.g.

data class ClothingWithSingleSubCategory (
    @Embedded
    val clothing: Clothing,
    @Relation(
        entity = SubCategory::class,
        parentColumn = "subCategoryReference",
        entityColumn = "id"
    )
    val subCategory: SubCategory
)

You could then have the following as an @Dao annotated class :-

@Dao
interface AllDao {
    @Insert(onConflict = IGNORE)
    fun insert(clothing: Clothing): Long
    @Insert(onConflict = IGNORE)
    fun insert(subCategory: SubCategory): Long
    @Transaction
    @Query("SELECT * FROM clothing")
    fun getAllClothingWithSubCategory(): List<ClothingWithSingleSubCategory>
}

With a suitable @Database annotated class you could then have something like the following in an activity:-

class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        val sc_popular = dao.insert(SubCategory(null,"Popular"))
        val sc_outerwear = dao.insert(SubCategory(null,"OuterWear"))
        val sc_tops = dao.insert(SubCategory(null,"Tops"))
        val sc_swimwear = dao.insert(SubCategory(100,"Swimwear"))
        val sc_shoes = dao.insert(SubCategory(null,"Shoes"))
        val sc_dresses = dao.insert(SubCategory(null,"Dresses"))
        val sc_pyjamas = dao.insert(SubCategory(null,"Pyjamas"))

        dao.insert(Clothing(100200300400,sc_popular,"Jeans","jeans_image","blah","100.55"))
        dao.insert(Clothing(100200300500,sc_outerwear,"Anorak","anorak_image","blah","214.55"))

        for (cwsc: ClothingWithSingleSubCategory in dao.getAllClothingWithSubCategory()) {
            Log.d("DBINFO","Name = ${cwsc.clothing.name} Price is ${cwsc.clothing.price} Sub-Category is ${cwsc.subCategory.subCategoryName}")
        }

    }
}

When run the log would then include:-

D/DBINFO: Name = Jeans Price is 100.55 Sub-Category is Popular
D/DBINFO: Name = Anorak Price is 214.55 Sub-Category is OuterWear

Example many-many

Like the 1-many you will want a POJO BUT one that has a List of sub-categories obtained via the mapping table. This uses the @Embeded annotation and the @Relation annotation but extended to include the associateBy to inform Room about the intermediate table. So you could have:-

data class ClothingWithListOfSubCategories(
    @Embedded
    val clothing: Clothing,
    @Relation(
        entity = SubCategory::class,
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = ClothingSubCategoryMap::class,
            parentColumn = "clothingMap",
            entityColumn = "subcategoryMap"
        )
    )
    val subCategories: List<SubCategory>
)

The you could have the following in an @Dao annotated class:-

/* ADDED for many-many */
@Insert(onConflict = IGNORE)
fun insert(clothingSubCategoryMap: ClothingSubCategoryMap): Long
@Transaction
@Query("SELECT * FROM clothing")
fun getAllClothingWithSubCategories(): List<ClothingWithListOfSubCategories>

and if the activity were extended to include :-

    /* Added for many-many */
    /* Note utilises clothing and sub-categories above */
    dao.insert(ClothingSubCategoryMap(jeans,sc_popular))
    dao.insert(ClothingSubCategoryMap(jeans,sc_swimwear))
    dao.insert(ClothingSubCategoryMap(jeans,sc_shoes))
    dao.insert(ClothingSubCategoryMap(anorak,sc_popular))
    dao.insert(ClothingSubCategoryMap(anorak,sc_outerwear))

    for(cwlsc: ClothingWithListOfSubCategories in dao.getAllClothingWithSubCategories()) {
        Log.d("DBINFO","Name = ${cwlsc.clothing.name} Price is ${cwlsc.clothing.price} it is in ${cwlsc.subCategories.size} sub-categories. They are:-")
        for(sc: SubCategory in cwlsc.subCategories) {
            Log.d("DBINFO","\t${sc.subCategoryName}")
        }
    }

The the log would also include :-

D/DBINFO: Name = Jeans Price is 100.55 it is in 3 sub-categories. They are:-
D/DBINFO:   Popular
D/DBINFO:   Swimwear
D/DBINFO:   Shoes
D/DBINFO: Name = Anorak Price is 214.55 it is in 2 sub-categories. They are:-
D/DBINFO:   Popular
D/DBINFO:   OuterWear