How to save an object with a list as attribute in a SQL Database

Question

I want to store a list of Day objects in my sqflite database. The Day class looks like this:

class Day {
  String date; 
  List<DoneTask> doneTasks;
  double score = 0;

Day({this.doneTasks, this.date});
}

the DoneTask class:

class DoneTask {
  Category category;
  double score;
  String description;

DoneTask({this.category, this.score, this.description});
}

Category has an attribute id, which is all I want to store from that.

I'm not sure how I can realize that with sqflite. I was thinking about adding the Attribute String day to the DoneTasks class for loading the DoneTasks in first, and sort them into the Days later. But this does not sound like a good solution for me, has anyone an idea how I could do it in a better way?

I'm very new to using SQL, so id appreciate simple answers/

(this is what I used yet for sqflite: https://flutter.dev/docs/cookbook/persistence/sqlite)

Answer 1

Here is how I would save it :

• First create a table to save your Day object. Inside it you will save only the date and score properties.

The method Database().insert return a Future<int> which is the id of the newly created row so you can use it to save your DoneTask and link them to the Day.

• Now, you can save each of your List<DoneTask> in another table with a column id_day as their identifier.

Here is a modelization of what it could look.

Answer 2

I would recommend going with a noSQL database if that is the data architecture you are going for. Of course, I do not know the scope of your entire project thus it is possible that an SQL database is a better fit for some reason that is unknown to me. But given just what you have presented, a noSQL alternative to sqflite seems like a better option. This would allow records to be stored effectively as objects, allowing you to store objects within objects, rather than having to create a bunch of tables cross referencing one another. It just seems more intuitive to me to do it that way.