I'm trying to write a clean, maintainable, and easy to query database design for a habit tracking application.
Users can have habits, which have a goal of X minutes or repetitions every Y interval, along with a schedule for how often or which days this habit is required on.
The database has 6 main tables, which I'll briefly describe
- habits (holds all habits, typically belonging to different users)
- habit_goals (holds the goal needed for completion)
- habit_progress_logs (stores committed effort towards goal)
- habit_schedules (used to set schedule, exclude days etc)
habit_schedules's recurrence column can have values like below in order to determine which days to include / exclude:
It created possibilities like so:
- Harry can complete 30 minutes of Reading every Thursday and Friday
- Jane can go for a bike ride twice a day, every day
- Peter can meditate every day of the week except Sundays
- Hannah does cooking once every 11 days
I have created an initial design, although it seems messy and difficult to query, especially in a sense of working out the progress of if a habit is completed, half done, understanding consecutive day streaks, and more.
To work out which habits are completed for a specific user I have to do something like this, which seems rather complex:
SELECT * FROM habits INNER JOIN logmylife.habit_goals hg on habits.id = hg.habit_id WHERE habits.user_id = 1 AND (SELECT SUM(value) FROM habit_progress_logs WHERE habit_progress_logs.created_at > DATE_SUB(NOW(), INTERVAL hg.`interval` SECOND) AND habit_progress_logs.habit_id = habits.id ) > hg.value
It gets even more complicated when I have to ignore skips and/or failures, check schedules for excluded days (haven't even attempted yet), and more.
How can I make this structure better?