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.

enter image description here

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:


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?


  • Will this system ever be used to for breaking bad habits or it's only for maintaining good ones?
    – J.D.
    Aug 14 at 13:57
  • In the future yes, although I'm looking to get it working efficiently with just good habits for now.
    – jacks
    Aug 14 at 15:40
  • Understood. I ask because it may drive your design decision though, since that's a bit of the converse of use cases. For queries where you're looking to see if something happened (e.g. rows representing maintaining a good habit exist), you'll instead want to possibly check for the lack of something happening (e.g. rows for a bad habit occurrence don't exist).
    – J.D.
    Aug 14 at 19:21


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.