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
- habit_groups
- 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:
- RRULE:BYDAY=WE
- RRULE:BYDAY=WE,TH,FR
- RRULE:FREQ=DAILY;INTERVAL=3
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?
