I am new to databases. I am not a professional developer. I would like your advice. I want to create a database that manages the students in a class. Students belong to only one class. I present to you my model. Can you let me know if this is correct please?
My data class: Gru
@Entity(tableName = "groupe_table")
data class Gru (
@PrimaryKey(autoGenerate = true) @ColumnInfo(name = "idGroup") var idGroup: Int=0,
@ColumnInfo(name = "nameGroupG") var nameGroupG : String
):Parcelable
@Entity(tableName = "user_table", foreignKeys = arrayOf(
ForeignKey(entity = Gru::class,
parentColumns = arrayOf("idGroup"),
childColumns = arrayOf("id"),
onDelete = ForeignKey.CASCADE)
))
@Parcelize
data class User(@PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") var id: Int=0,
@ColumnInfo(name = "nameGroup") var nameGroup: String,
@ColumnInfo(name = "firstName") var firstName: String,
@ColumnInfo(name = "lastName") var lastName: String,
@ColumnInfo(name = "nbTeam") var nbTeam: String
):Parcelable
@Entity(tableName = "eval_table", foreignKeys = arrayOf(
ForeignKey(entity = User::class,
parentColumns = arrayOf("id"),
childColumns = arrayOf("idEval"),
onDelete = ForeignKey.CASCADE)
))
data class Eval(@PrimaryKey(autoGenerate = true) @ColumnInfo(name = "idEval") var idEval: Int=0,
@ColumnInfo(name = "note_classement") var note_classement: String,
@ColumnInfo(name = "note_attaque") var note_attaque: String,
@ColumnInfo(name = "note_passe") var note_passe: String,
@ColumnInfo(name = "note_afl2") var note_afl2: String,
@ColumnInfo(name = "note_afl3") var note_afl3: String,
@ColumnInfo(name = "note_sur_vingt") var note_sur_vingt: String)
Here my dataclass to create relations
data class GruWithUser(
var idGroup: Int,
var nameGroupG: String,
var id: Int,
var nameGroup: String,
var firstName: String,
var lastName: String,
var nbTeam: String
):Parcelable
and the last dataclass relation: User With Eval
@Parcelize
data class UserWithEval(
var id: Int,
var nameGroup: String,
var firstName: String,
var lastName: String,
var nbTeam: String,
var note_attaque: String,
var note_passe: String,
var note_classement: String,
var note_afl2: String,
var note_afl3: String,
var note_sur_vingt: String
): Parcelable
Thanks you so much for your help
Issue 1
You appear to have an issue that will likely cause some frustration if not addressed.
That is a User, has it's primary key as the reference to the parent group (Gru). As such a Group could only have a single User (Student) as the primary key, for the User must be unique.
Likewise for Eval's.
So you could consider the following:-
@Entity(tableName = "groupe_table")
data class Gru (
@PrimaryKey(autoGenerate = true) @ColumnInfo(name = "idGroup") var idGroup: Int=0,
@ColumnInfo(name = "nameGroupG") var nameGroupG : String
): Parcelable
@Entity(tableName = "user_table", foreignKeys = arrayOf(
ForeignKey(entity = Gru::class,
parentColumns = arrayOf("idGroup"),
//childColumns = arrayOf("id"), //<<<<< REMOVED
childColumns = ["gru_id_reference"], //<<<<< REPLACED WITH
onDelete = ForeignKey.CASCADE)
))
@Parcelize
data class User(@PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") var id: Int=0,
@ColumnInfo(name = "nameGroup") var nameGroup: String,
@ColumnInfo(name = "firstName") var firstName: String,
@ColumnInfo(name = "lastName") var lastName: String,
@ColumnInfo(name = "nbTeam") var nbTeam: String,
@ColumnInfo(index = true) //<<<<< ADDED (may be more efficient)
var gru_id_reference: Int //<<<<< ADDED
):Parcelable
@Entity(tableName = "eval_table", foreignKeys = arrayOf(
ForeignKey(entity = User::class,
parentColumns = arrayOf("id"),
childColumns = arrayOf("user_id_reference"), //<<<<< CHANGED
onDelete = ForeignKey.CASCADE)
))
data class Eval(@PrimaryKey(autoGenerate = true) @ColumnInfo(name = "idEval") var idEval: Int=0,
@ColumnInfo(name = "note_classement") var note_classement: String,
@ColumnInfo(name = "note_attaque") var note_attaque: String,
@ColumnInfo(name = "note_passe") var note_passe: String,
@ColumnInfo(name = "note_afl2") var note_afl2: String,
@ColumnInfo(name = "note_afl3") var note_afl3: String,
@ColumnInfo(name = "note_sur_vingt") var note_sur_vingt: String,
@ColumnInfo(index = true) var user_id_reference: Int //<<<<< ADDED
)
Without going into all the other code, the following code:-
db = TheDatabase.getInstance(this)
dao = db.getAllDao()
val g1id = dao.insert(Gru(nameGroupG = "Group001"))
val g2id = dao.insert(Gru(nameGroupG = "Group002"))
val g3id = dao.insert(Gru(nameGroupG = "group003"))
val u1id = dao.insert(User(nameGroup = "Why have this here?", firstName = "Fred", lastName = "Bloggs", nbTeam = "TeamA", gru_id_reference = g1id.toInt()))
val u2id = dao.insert(User(nameGroup = "?????", firstName = "Jane", lastName = "Doe", nbTeam = "TeamX", gru_id_reference = g1id.toInt()))
val u3id = dao.insert(User(nameGroup = "?????", firstName = "Mary", lastName = "Smith", nbTeam = "TeamB", gru_id_reference = g2id.toInt()))
val u4id = dao.insert(User(nameGroup = "?????", firstName = "Tom", lastName = "Cobbely", nbTeam = "TeamC", gru_id_reference = g3id.toInt()))
var baseEval = Eval(note_classement = "CMENT_", note_attaque = "ATTQ_", note_afl2 = "AFL2_", note_afl3 = "AFL3_", note_passe = "PASSE_", note_sur_vingt = "SV_",user_id_reference = -99)
for (i in 1..10) {
dao.insert(
Eval(
note_classement = baseEval.note_classement + i,
note_attaque = baseEval.note_classement + i,
note_afl2 = baseEval.note_afl2 + i,
note_afl3 = baseEval.note_afl3 + i,
note_passe = baseEval.note_passe + i,
note_sur_vingt = baseEval.note_sur_vingt + i,
user_id_reference = Random.nextInt(4) + 1
)
)
}
results in a database (i.e. tests the changed code) as per :-
The 3 Groups (Gru's/Classes) :-
The 4 users :-
And the 10 Eval's spread across the 4 Users
A query that joins the data according to the relationships looks like:-
- here you can see that there are 3 evaluations for Group001, 2 of them for Fred and 1 for Jane etc
- (note Eval's reference a random User)
Issue 2
You may well encounter subsequent issues due to both the user_table and the eval_table having a column named id. From an SQL point of view this can be overcome by qualifying the column with it's table name (see SQL used above where the tablename .
column is used to disambiguate the ambiquity). However, as far as the resultant output there would still be 2 id columns. This ambiguity can be overcome using AS to rename the output column but you may then encounter issues. I would suggest ensuring that all column names are unique (so perhaps have column names userId and evalId instead of just id).