As noted in the comments, you should read up on relational database basics. The vocabulary for databases is different from programming. SQLite is a basic database engine and only supports basic data types, so after learning relational database concepts, you should read up on sqlite specifically.
Here's an informal summary for your problem:
- Your data model requires two tables as you noted.
- What you call 'Fields' are 'columns' in the tables. Columns have a type such as INTEGER or TEXT (https://www.sqlite.org/datatype3.html).
- Arrays are expressed with FOREIGN KEYs (https://sqlite.org/foreignkeys.html). A foreign key is a property that references a record in another column by matching a unique value in a specific column of the other table. You build an array of values by selecting records from the table with the foreign key that match the object you care about.
- Note that SQL code in an app generally isn't required. Try looking for a framework or Object Relational Mapper (ORM) that takes care of mapping database tables to objects, columns to properties and handles the persistence and schema updates for you. I can say from experience, manual SQL code is difficult to maintain and seriously complicates testing.
Your specific problem could be solved with this schema:
CREATE TABLE mainitem(
id INTEGER PRIMARY KEY,
field1 TEXT,
field2 INTEGER
);
CREATE TABLE image(
id INTEGER PRIMARY KEY,
datetaken TEXT,
path TEXT,
mainitem INTEGER,
FOREIGN KEY(mainitem) REFERENCES mainitem(id)
);
ALTER TABLE mainitem
ADD COLUMN mainimage INTEGER REFERENCES mainitem(id) DEFAULT NULL;
To get your array of images, where the id of your mainitem == 3:
SELECT * FROM image WHERE mainitem = 3;
To get your main item with its main image:
SELECT *
FROM mainitem
LEFT OUTER JOIN image
ON mainitem.mainimage = image.id
WHERE mainitem.id = 3;
