Structuring DB tables and @Relation to create a questionaire from db entities? (kotlin, Room)-CodePudding

Iam seeking a little guidence before diving into a new project.

My aim is to produce a form/questionaire within an app that pulls Question data, based on a question's Group and its Category. Where each Category contains many Groups, and each Group contains many Questions.

To support the UI and nested recycler views, the intent was to provide the viewmodel with a single object that contains nested lists. i.e An object with a list of Categories, containing a list of Groups, containing a list of Questions.

In terms of setting up room, entities and DAOs, and their relationships, my understanding is that that the best way to achieve this is to:

Create a Questions entity (conatining text,options..etc.)
Create a reference table for the relationship between Questions and Groups (many-to-many)
Create a parent/child table for the relationship between Groups and Categories (one-to-many)

Following this a set of relation based data classes should be use to marry up each pair.

GroupWithQuestions data class (using @Relation to list questions in each group using the reference table)
CategoryWithGroupsWithQuestions data class (using @Relation to list groups in each category using parent/child table)
QuestionaireWithCategoriesWith...Questions data class (containing a list of CategoryWithGroupsWithQuestions)

This is complicated, relationships need to be followed through multiple tables, and thus will be hard to update and time consuming to resolve errors. I feel like Im over thinking the approach (or am missing something).

Is there a simpler/smarter way?

(Is the single object approach part of the problem?)

Thanks in advance for your suggestions and comments.

CodePudding user response：

Create a Questions entity (conatining text,options..etc.) Create a reference table for the relationship between Questions and Groups (many-to-many) Create a parent/child table for the relationship between Groups and Categories (one-to-many)

A one-many simply needs a column in the child for the parent.

This is complicated, relationships need to be followed through multiple tables, and thus will be hard to update and time consuming to resolve errors. I feel like Im over thinking the approach (or am missing something).

This isn't really that complicated: I believe the following is pretty close to what you appear to be asking for:-

Entities

Category :-

@Entity(
    indices = [
        Index(value = ["categoryName"],unique = true) /* Assume that a category name should be unique */
    ]
)
data class Category(
    @PrimaryKey
    val categoryId: Long? = null,
    @ColumnInfo
    val categoryName: String
)

Group :-

@Entity(
    foreignKeys = [
        ForeignKey(
            entity = Category::class,
            parentColumns = ["categoryId"],
            childColumns = ["categoryIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)

data class Group(
    @PrimaryKey
    val groupId: Long? = null,
    @ColumnInfo(index = true)
    val categoryIdMap: Long,
    val groupName: String
)

Foriegn Key constraints are not necessary but they help to enforce referential integrity.
- onDelete and onUpdate aren't necessary but can be helpful

Question

@Entity(
)
data class Question(
    @PrimaryKey
    val questionId: Long? = null,
    @ColumnInfo(index = true)
    val questionText: String,
    val questionOption: Int
)

QuestiongroupMap (could be GroupQuestionMap) :-

@Entity(
    primaryKeys = ["questionIdMap","groupIdMap"],
    foreignKeys = [
        ForeignKey(
            entity = Question::class,
            parentColumns = ["questionId"],
            childColumns = ["questionIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE),
        ForeignKey(
            entity = Group::class,
            parentColumns = ["groupId"],
            childColumns = ["groupIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class QuestionGroupMap(
    val questionIdMap: Long,
    @ColumnInfo(index = true)
    val groupIdMap: Long
)

POJO's

GroupWithQuestions

data class GroupWithQuestions(
    @Embedded
    val group: Group,
    @Relation(
        entity = Question::class,
        entityColumn = "questionId",
        parentColumn = "groupId",
        associateBy = Junction(
            QuestionGroupMap::class,
            parentColumn = "groupIdMap",
            entityColumn = "questionIdMap"
        )
    )
    val questionList: List<Question>
)

via the QuestiongroupMap and hence the association and Junction

CategoryWithGroupsWithQuestions

data class CategoryWithGroupWithQuestions(
    @Embedded
    val category: Category,
    @Relation(entity = Group::class,entityColumn = "categoryIdMap",parentColumn = "categoryId")
    val groupWithQuestionsList: List<GroupWithQuestions>
)

NOTE even though you are getting a List of GroupWithQuestions, it is the Group entity that is specified.

Some extras that may be of use :-

data class CategoryWithGroup(
    @Embedded
    val category: Category,
    @Relation(entity = Group::class,entityColumn = "categoryIdMap",parentColumn = "categoryId")
    val group: Group
)

data class GroupWithCategory(
    @Embedded
    val group: Group,
    @Relation(entity = Category::class,entityColumn = "categoryId",parentColumn = "categoryIdMap")
    val category: Category
)

Dao's

AllDao (i.e. all in one place for brevity/convenience) :-

@Dao
abstract class AllDao {

    @Insert
    abstract fun insert(category: Category): Long
    @Insert
    abstract fun insert(group: Group): Long
    @Insert
    abstract fun insert(question: Question): Long
    @Insert
    abstract fun insert(questionGroupMap: QuestionGroupMap): Long
    @Transaction
    @Query("SELECT * FROM `group`")
    abstract fun getAllGroupsWithCategory(): List<GroupWithCategory>
    @Transaction
    @Query("SELECT * FROM category")
    abstract fun getAllCategoriesWithGroups(): List<CategoryWithGroup>
    @Transaction
    @Query("SELECT * FROM `group`")
    abstract fun getAllGroupsWithQuestions(): List<GroupWithQuestions>

    @Transaction
    @Query("SELECT * FROM category")
    abstract fun getAllCategoriesWithGroupsWithQuestions(): List<CategoryWithGroupWithQuestions>

}

an @Database class TheDatabase :-

@Database(entities = [Category::class,Group::class,Question::class,QuestionGroupMap::class],exportSchema = false,version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        @Volatile
        private var instance: TheDatabase? = null

        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(
                    context,
                    TheDatabase::class.java,
                    "thedatabase.db"
                )
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

allowMainThreadQueries for brevity/convenience

Finally putting the above into action in an activity, result in a list of CategoreiesWithgroupsWithQuestions being extracted and output to the log :-

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 TAG = "DBINFO"

        val cat1 = dao.insert(Category(categoryName = "Cat1"))
        val cat2 = dao.insert(Category(categoryName = "Cat2"))
        val cat3 = dao.insert(Category(categoryName = "Cat3"))

        val grp1 = dao.insert(Group(groupName = "Grp1",categoryIdMap = cat1))
        val grp11 = dao.insert(Group(groupName = "Grp11",categoryIdMap = cat1))
        val grp111 = dao.insert(Group(groupName = "Grp111",categoryIdMap = cat1))
        val grp1111 = dao.insert(Group(groupName = "Grp1111",categoryIdMap = cat1))
        val grp2 = dao.insert(Group(groupName = "Grp2",categoryIdMap = cat2))
        val grp22 = dao.insert(Group(groupName = "Grp22",categoryIdMap = cat2))
        val grp3 = dao.insert(Group(groupName = "Grp3",categoryIdMap = cat3))

        val q1 = dao.insert(Question(questionText = "Q1 ....", questionOption = 11110000))
        val q2 = dao.insert(Question(questionText = "Q2....", questionOption = 11010101))
        val q3 = dao.insert(Question(questionText = "Q3....", questionOption = 10000001))
        val q4 = dao.insert(Question(questionText = "Q4....",questionOption = 11000001))
        val q5 = dao.insert(Question(questionText = "Q5....",questionOption = 11100011))

        dao.insert(QuestionGroupMap(q1,grp1))
        dao.insert(QuestionGroupMap(q1,grp2))
        dao.insert(QuestionGroupMap(q1,grp3))
        dao.insert(QuestionGroupMap(q2,grp2))
        dao.insert(QuestionGroupMap(q2,grp22))
        dao.insert(QuestionGroupMap(q3,grp3))
        dao.insert(QuestionGroupMap(q4,grp11))
        dao.insert(QuestionGroupMap(q4,grp111))
        dao.insert(QuestionGroupMap(q4,grp1111))
        dao.insert(QuestionGroupMap(q5,grp22))

        /* extract the data via the geAllCategoriesWithGroupsWithQuestions query*/
        for (cwgwq: CategoryWithGroupWithQuestions in dao.getAllCategoriesWithGroupsWithQuestions()) {
            Log.d(TAG,"Category is ${cwgwq.category.categoryName} ID is ${cwgwq.category.categoryId}, it has ${cwgwq.groupWithQuestionsList.size} groups, which are:-")
            for(gwq: GroupWithQuestions in cwgwq.groupWithQuestionsList) {
                Log.d(TAG,"\tGroup is ${gwq.group.groupName} ID is ${gwq.group.groupId}, it has ${gwq.questionList.size} questions, which are:-")
                for(q: Question in gwq.questionList) {
                    Log.d(TAG,"\t\tQuestion is ${q.questionText} options are ${q.questionOption} ID is ${q.questionId}")
                }
            }
        }

    }
}

result :-

D/DBINFO: Category is Cat1 ID is 1, it has 4 groups, which are:-
D/DBINFO:   Group is Grp1 ID is 1, it has 1 questions, which are:-
D/DBINFO:       Question is Q1 .... options are 11110000 ID is 1
D/DBINFO:   Group is Grp11 ID is 2, it has 1 questions, which are:-
D/DBINFO:       Question is Q4.... options are 11000001 ID is 4
D/DBINFO:   Group is Grp111 ID is 3, it has 1 questions, which are:-
D/DBINFO:       Question is Q4.... options are 11000001 ID is 4
D/DBINFO:   Group is Grp1111 ID is 4, it has 1 questions, which are:-
D/DBINFO:       Question is Q4.... options are 11000001 ID is 4
D/DBINFO: Category is Cat2 ID is 2, it has 2 groups, which are:-
D/DBINFO:   Group is Grp2 ID is 5, it has 2 questions, which are:-
D/DBINFO:       Question is Q1 .... options are 11110000 ID is 1
D/DBINFO:       Question is Q2.... options are 11010101 ID is 2
D/DBINFO:   Group is Grp22 ID is 6, it has 2 questions, which are:-
D/DBINFO:       Question is Q2.... options are 11010101 ID is 2
D/DBINFO:       Question is Q5.... options are 11100011 ID is 5
D/DBINFO: Category is Cat3 ID is 3, it has 1 groups, which are:-
D/DBINFO:   Group is Grp3 ID is 7, it has 2 questions, which are:-
D/DBINFO:       Question is Q1 .... options are 11110000 ID is 1
D/DBINFO:       Question is Q3.... options are 10000001 ID is 3