Moor flutter List<MyTable> datatype

Question

I'm using moor_flutter to persist my data in an SQLite database in my Flutter application. One of my columns for the sake of argument looks like this:

class Clients extends Table {
  // Autoincrement automatically sets this to be a primary key
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text().withLength(min: 1, max: 60)();
  TextColumn get phone => text().withLength(min: 1, max: 50)();
}

I'm looking to store an array of clients I have recently opened in a List of length 10. Every time somebody would interact with that client, the list would place that client on the top of the list and remove the last one so that it would always be 10. It could also reorder them to bubble one from the middle, up to the top.

In any case, what I'm looking to do is create a new table called "Recents" which would have a single field of type List like so:

class Recents extends Table {
  List<Client> recents => List<Client>[10].withLength(min: 0, max:10)()();
}

I couldn't find anything usable on the interwebz so here we are StackOverflowers! How would you solve this conundrum?

Answer 1

1. There isn't any good way in the SQLite database to store a list in a single column of a table the natural way is to store list items in the other table which every single row represents a single item of your list and connect that with a relation to other tables for example for if consider a scenario which we have a Users table and each user entity have a list of phone numbers that associated with we can store all the users in the Users table and store all the phone numbers in other table and we will create a userId column in the phone numbers table and we add a userId for each phone number that indicates this phone number is associated with the specific user with unique userId "the relation" and this way when we want to get a phone numbers list for a user we can query phone numbers table for the phone numbers that have specific userId

Users Table

UserId	Username
0	A
1	B
3	C

Phone Numbers Table

Id	PhoneNumber	UserId
0	+099888	0
1	+121181288	0
2	+31188218	0
3	+121221295	1
4	+9852121328	2
5	+2512165	2

2. the second way you can handle this scenario is converting your List to a type that is supported in SQLite for example convert your list items to a JSON string and insert that as text into a column of a user table and when you read this field you can convert it to a List of items, Most of ORM's will provide a tool that helps you with us a secnario for a moor, you can use a converter here is the link to the documentation https://moor.simonbinder.eu/docs/advanced-features/type_converters/

But notice that this approach is less flexible for example you can't query on the phone numbers and it has a bad effect on performance