Fork me on GitHub

I have a data modeling and schema learning challenge. I am building a task tracker that can probably be best expressed as a daily exercise tracker (i.e. 2 sets of 10 reps of push-ups daily). It will track more than that, but the workout metaphor works well. Each day the goals will renew, and I’ll record sets completed throughout the day (maybe one set in the morning, and another one later). I’m a bit lost on how I’m going to model this, though it seems like I have two document types (tasks and sets). First off, should I proceed here or take this to #CG3AM2F7V? I’m happy either way.


Just me again. It would seem a ‘completed set’ would have a reference to a task; I haven’t yet delved into how to relate things in xtdb (‘foreign key’ comes to mind from other db’s but I may be off). Forgive me as I explore my thoughts in this comment with edits. ‘Sets’ is probably a bad name, or maybe it’s the right one. I have to have a name to record the completion of a set… maybe ‘effort’. One last twist I can think of that I should consider going in: the size of a set will change on any given day, and the number of sets can change as well to comprise a task completion.


To relate docs in XTDB, set the value of an attribute to an :xt/id or a vector/set of :xt/ids.


Thanks! So, hmm…

:task/id :uuid
   :task/name :string
   :task/sets :int
   :task/reps-per-set :int
   :task/description :string
   :task [:map {:closed true}
          [:xt/id :task/id]

   :effort/id :uuid
   :effort/task :task/id
   :effort [:map {:closed true}
            [:xt/id :effort/id]
I’ll play around with this and see if it works.

Jacob O'Bryant06:11:06

hm, yeah having two document types like that sounds reasonable. schema looks good. "I'll play around with this and see if it works" is pretty much the best strategy :). hard to know the best way to model stuff until you know how you're planning to access/modify it.

Jacob O'Bryant06:11:58

depending on how the app will work, I wonder if you'd want to have reps-per-set be a key on the set/effort itself? (maybe you're planning to do that in addition to having :task/reps-per-set on the task document, so you know how many reps a new set document should have)


I’ll have to think about that. FWIW I’m thinking of stacked cards with one per task; a tap on the card will increment the set count (i.e. 0/5 -> 1/5) and when it reaches 5/5 it’ll change to show that the goal was met; I’ll have to put in a decrement ui element to be able to undo accidental increments.


ok, yeah… great suggestion. I should put the reps completed on the effort itself, in addition probably to being on the task. also, I should be able to adjust the ‘10 rep sets’ and ‘0/5’ (the number to complete) whenever I want, and that will update the task in the db. the next day might then just start with whatever they were last set to. exercise is a great example for this… you get a cold and dial the goals down for the day.


I’ll be wanting to use this for any goals I have, not just exercise or whatever… just trying to gamify habits a bit and also be able to record efforts easily and also keep track of streaks.


But it’s all an excuse to learn how to relate documents in xtdb/datalog and the accompanying queries and transactions.

Jacob O'Bryant17:11:21

will all the sets on a particular day have the same number of reps? maybe instead of a document for each set, you should have a document for each day (or to be explicit, for each day/task combination). when the app loads, it queries for all the task documents. then for each task document it looks up a task-day document for the current day. if the doc doesn't exist, then the number of completed sets for that day is 0. and then incrementing/decrementing the number of completed sets for today is just incrementing/decrementing a number on that document.

Jacob O'Bryant17:11:05

will you need to know the time of day at which each set is completed?


The day/task approach sounds awesome. That will also make it easier to go back in history and look at previous days. I added in a ‘recorded-time’ key to the document after I posted last night; I know I often won’t enter things in until after the fact so that’s more accurate. But some idea of when it was entered will be good to have. I will want to change the reps-per-set/set-number-for-completion (not my actual keys, just hyphenating for clarity here) on the fly each day; that’s a common pattern for me… I realize I want to adjust the number or amount down of whatever my goal is, then it will stay there for a stretch (days or weeks)… so the spawned day/task thing each day should probably be based on the previous value. I can imagine setting goals that increment over days (scheduling) but that’s definitely preemptive optimization at this point.


I’m assuming if I dial the reps or sets or whatever on any given day xtdb it’ll just give a new value to that key for that day’s task/sets document and the next transaction/query will just reflect that. And I’ll have a history of the previous value, of course, for free with xtdb.


Here’s what I had at the end of yesterday, and I just added a valid-day key on the task… it’s a string because I’m not sure what would be best for that (although there is the whole XTDB valid until world… maybe that is what would apply here, but seems probably intended for other purposes):

:task/id :uuid
   :task/valid-day :string
   :task/name :string
   :task/sets :int
   :task/reps-per-set :int
   :task/description :string
   :task [:map {:closed true}
          [:xt/id :task/id]

   :work/id :uuid
   :work/task :uuid
   :work/recorded-at inst?
   :work [:map {:closed true}
          [:xt/id :work/id]


So, I’ve put a key on the task that would store how many sets were completed…

:task/id :uuid
   :task/valid-day :string
   :task/name :string
   :task/sets :int
   :task/reps-per-set :int
   :task/sets-completed :int
   :task/description :string
   :task [:map {:closed true}
          [:xt/id :task/id]
and now I don’t think I need the work document which is what I think you were getting at. Now it’s time for me to try to get that working.


heading down this rabbit hole… yell down it if it’s not a sane one for this problem:

Jacob O'Bryant23:11:16

I'd probably still recommend having a separate document to track work done on a task for a specific day. you can possibly get it to work by putting it all on the task document and relying on xtdb's history features to get data for previous days, however I think it would be more awkward than using separate documents. though it doesn't hurt to try it out either way!


   {:xt/id :malcolm :name "Malcolm" :last-name "Sparks"}
   #inst "1986-10-22"]])


(xt/q (xt/db node #inst "1986-10-23") q)
The date is what I needed… just a day… I was expecting to have to munge a long java date/time string.


I’m rooting around for how that would-be/was generated (the day instance as opposed to minutes/seconds/etc).

Jacob O'Bryant23:11:49

kind of my rule of thumb is that if you're going to regularly access historical data, it's probably better to save it "directly" in the db, and then mainly use XT's history features for less frequent stuff. e.g. I think xt history would be a good fit for showing the edit history of a comment. but in your case, if you wanted to, say, show a calendar view with your task progress on previous days, I'd go with the explicit document approach

👍 1
Jacob O'Bryant23:11:25

there's a biff/crop-day function you can use for this! it chops off the time portion of a date

Jacob O'Bryant23:11:55

also, if you do the explicit doc approach and you do want the individual times each set was recorded, you could use a vector, like {:task-day/recorded-at [#inst "..."]} when you increment the set count, add a time to the end of the vector, and vice versa


By explicit doc approach, do you mean recording the set on the task as opposed to a separate ‘work’ doc?

Jacob O'Bryant23:11:08

I mean having a separate work doc (I called it "task-day" in my example above, but either way)


Aha. Yeah, I was a bit confused by that… were you thinking of a task doc and then a task-day doc?

Jacob O'Bryant23:11:17

I.e. if I have 2 tasks and I work on each of them daily for a week, then I'd have 2 task documents and 14 work documents

Jacob O'Bryant23:11:47

(I'll just call it a work document from now on instead of a task-day document)

Jacob O'Bryant23:11:11

and a work document would be uniquely identified by the task it refers to and by a particular day, Eg. you'd have a :work/task key and a :work/day key


bam! getting it now. and this is great, especially because it will be easier to reason about units of work and also query them for visualizations.

🎅 1

ok, lets see how many feet I can go implementing this. so awesome to be able to make and modify these schemas right in a repl connected editor, then make a ui for the data as easily.


Currently ruining my reputation over in #xtdb atm… asking about how to find all the documents for a given day. I realize I’ll want to be able to back to any previous day as well.


I could convert to a string and remove the time with the Biff utility then compare to a string representing the date I want… or maybe I can compare the dates in their native format (again, with the time chopped off)… I’m just sanity checking to make sure there isn’t a better route.


I got pointed in the right direction. And also discovered the various tutorials on the Java date/time tools and paradigm is where I need to look to learn more.

Jacob O'Bryant01:11:13

👌 what I would probably do myself in this situation is to have a :work/day key, and use (biff/crop-day (java.util.Date.)) as the value. then when you query for documents on a given day, you can pass in (biff/crop-day some-day) as the parameter


You know what? I think I’ll give that a try right now. 😀 UPDATE: worked like a charm, just minutes to implement.