How to create nested tables in a SQLite database? (Android)

Question

I want to create a nested sqlite database in android. i.e. I want a specific field in a table to have its new set of values ​​as a separate table.

Answer 1

What you describe is impossible; there is no way to include a table in a row in another table. Standard practice is to create “parent / child” tables by including the primary key of the parent table as a column in the child table; eg:

PARENT TABLE id | name --------- 1 | Fred 2 | Bob 

---

 CHILD TABLE id | parent_id | name --------------------- 1 | 1 | John 2 | 1 | Jim 3 | 2 | Joe 4 | 2 | Jane 

This pair of tables would have “John” and “Jim,” like the children of “Fred,” “Joe,” and “Jane,” like the children of “Bob.” You can get a set of all Bob's children (parent id = 2) with the request:

 SELECT * FROM child_table WHERE parent_id = 2 

Answer 2

This is what I was going to do, but you cannot create another subtable of the current table.

I suggest you create two different tables from it, for example, if you have a table employee and you need to create two more marketing employees for subcategories and employee table workers, just create the two tables that I described. eg

 CREATE TABLE emp_engineer 

and

 CREATE TABLE emp_marketing