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.