Best practice for a SQLite structure of a mobile diary app?


#1

I’m creating a mobile app with Ionic where I log multiple activities (physical and mental ones).

Won’t dive into details: simply I have different forms that user compiles daily to log these activities into a db.

Forms are different because activities are different (“Ate a meal”, “Studied for 3 hrs”, “Ran to work”, …).

I don’t know what it’s better, if

SOLUTION 1) a Wordpress-like solution as a main table for basic entry data and another big table dedicated to variable options or

SOLUTION 2) a main table for basic entry data and a new detailed table for every category.

Example:

SOLUTION 1)

MAIN_ENTRIES table:

id integer,
entry_title text,
entry_datetime text,
recurrency integer,
recurrency_type integer,
recurrency_init_date text,
recurrency_end_date text

ENTRIES_OPTIONS table:

id integer,
main_entries_id integer,
entry_option_name text,
entry_option blob

where entry_option cell is thought as meta_value cell in wp_postmeta in a Wordpress db.

SOLUTION 2)

MAIN_ENTRIES table:

id integer,
entry_title text,
entry_datetime text,
recurrency integer,
recurrency_type integer,
recurrency_init_date text,
recurrency_end_date text

then I’d have a table for every entry type: something like

PHYSICAL_ACTIVITIES table

id integer,
main_entries_id integer,
duration integer,
intensity integer, 
short_description text,
long_desc blob

MENTAL_ACTIVITIES table

id integer,
main_entries_id integer,
duration integer,
difficulty integer, 
effectiveness integer, 
short_description text,
long_desc blob

and so on.

About the former: I think it’s the more scalable solution but it can be harder to retrieve data from the blob field in the ENTRIES_OPTIONS table… maybe I’d have to write some sort of JSON data into it and create functions to retrieve different values.

About the latter: It’s probably the most “expensive” about the performance but to me it’s the clearest to write and mantain.

Don’t know what to choose, any help appreciated, thank you in advance.