Sqlite3 store list in column

I havea some data i need to store in a sqlite database that looks like this

[
   [user1,[python,java,javascript],21],
   [user2,[csharp,python,c,java,php,sql],18],
   [user3,[],52]
   [user4,[python],73]
]

How do i store the list of programming languages for each user in sqlite3

Answer

The standard solution is have a table for each thing: users, languages, and the table that holds the 1 to many relationship which in this case is users_languages. user and language are relatively large and variable sized key, so it’s pretty common optimization to introduce a an artificial key usually integer auto_increment.

create table languages (
   language text primary key
);

insert into languages values ('python'), ('java'), ('javascript'), ('csharp'), ('c'), ('php'), ('sql');

create table users (
   user text primary key,
   age tinyint not null
);

insert into users values ('user1', 21), ('user2', 18), ('user3', 52), ('user4', 73);    

create table users_languages (
  user text not null,
  language text not null,
  foreign key (user) references users (user),
  foreign key (language) references languages (language),
  unique(user, language)
);

insert into users_languages values ('user1', 'python'), ('user1', 'java'), ('user1', 'javascript');
 ...
 
 -- list of languages for a given user (row per language)
 select language from users_languages where user = '...';

 -- list of languages for all users (row per user)
 select user, group_concat(langauge) from users_langauges group by 1;