Store complex type in flutter sqflite

Question

how to store complex type in sqflite for example : the table person(id,name,email,List cars); where the car is an entity that have an id and a type ? is there any solution like how to store it (like a Blob or in a new table and link it to the person table ) ?

Answer 1

You can´t in a simple table. I understand your point, you want to store, for example, something like this:

citizen = {
  "name":"",
  "id":"",
  "childrens":{
     children1:{
       "name":"",
       "id":"",
     }
     children2:{
       "name":"",
       "id":"",
     }
  }
}

This is because sqflite is a tool to use the device sqlite and this DB works under apache software. That means, Sqflite stores data as well sql DB stores data and you can't store objects in a sql table.

You should create other table for each complex data as you need. Something like this (as pseudocode):

'CREATE TABLE Citizen ('
          ' id INTEGER PRIMARY KEY,'
          ' name TEXT,'
          ' children1Id INTEGER,'
          ' children2Id INTEGER,'
          '); '
'CREATE TABLE Children ('
          ' id INTEGER PRIMARY KEY,'
          ' name TEXT'
          ')'

I hope this will help you.

Answer 2

It's not really about mobile development and more about SQL DB and more generally speaking database management.

To answer directly you need create a table for car with an car-id and you need to know the relation between a person and a car to make the link:

• 1 - 1 : a person can only have one car and a car can only belong to one person
• 1 - n : a person can have multiple cars but a car can only belong to one person
• n - 1 : a person can only have one car but a car can have multiple owners
• n - n : finally the most complex case a person can only have car & a car can have multiple owners

In the three first case you set the id in the one (e.g 1-n : you put in the car table an field owner-id which is the id of the owner)

In the last case you need to create a matching table with : matching-id car-id person-id