Hi, I have a few suggestions regarding the database structure.
table Entries:
CREATE TABLE IF NOT EXISTS "Entries" (
"Id" bigint primary key not null , // use standard autoincrement id, so entries will have always unique id
"timestamp" bigint not null , // store timestamp in utc to avoid problems when entering entry in different timezones. Maybe store also timezone when the entry was created.
"Heading" varchar not null ,
"Text" varchar not null ,
"Rating" integer not null ,
"Latitude" float not null ,
"Longitude" float not null );
Lets have all tables their unique id as primary key. And reference to the Entries for DiaryEntryId.
CREATE TABLE IF NOT EXISTS "Media" (
"DiaryMediaId" varchar(36) primary key not null , // change it to autoinc integer. it will be much faster in searching
Not sure here if store media into database. Pictures/ videos can be quite large for sqlite. Maybe store just path to directory with stored media?
