How to store a list of objects in sqflite?

Question

I want to use the sqflite package to store my objects. I read some articles, tutorials and examples about it. So far I understand everything. But none of them covers my use case:

I have a class named Foo. This class holds beside some primitive fields an object of the class Bar. Bar has only primitive fields. Additionally Foo has a list of objects of the type Baz. Baz has also only primitive fields.

class Foo {
  // simple fields

  String id;

  Bar bar;
  List<Baz> bazs;
}

class Bar {
  // only primitive fields
  
  String id;
}

class Baz {
  // only primitive fields

  String id;
}

I want to connect the bar object with the foo object in the database and the same with the bazs objects. How do I do it? Whats the best way?

I'm not really experienced wit SQL. I know what SELECT, WHERE, ORDERBY and so on means but my knowledge does not get much further.

I found this qustion in Stack Overflow but I wonder if it is actually efficient to store all the values as a JSON. What happens if the JSON gets really big?

Currently my only idea is to store the id of the bar object as a field in the foo object or vica versa. foo could also hold a list of the ids of the bazs objects. Or each baz objects holds the id of the foo object. Is this a good solution?

Is there a possibility to let sqflite handle the relations between the objects?

Solution

You need to create 3 tables:

1. Foos: [fields]:

• id

2. Bars: [fields]:

• foo_id: a foreign key that points to the id field of Foo
• bar_id: which is the bar's id

3. Bazs: [fields]:

• foo_id: a foreign key that points to the id field of Foo
• baz_id: which is the baz's id

Now lets say that you want fetch a Foo record with and Id = 3 along with the bar and bazs

1. fetch the foo record:

SELECT * FROM foo WHERE id = 3;

2. fetch bar where foo_id = 3

SELECT bar_id
FROM bars
INNER JOIN Foo on B.id = bars.foo_id;

3. fetch bazs where foo_id = 3

SELECT baz_id
FROM bazs
INNER JOIN Foo on B.id = bazs.foo_id;

It would be easier if you use moor package to implement a sqllite DB