Create sprint poker backend data model for templating v1

[tiffanyhan]

Acceptance Criteria

• [ ] Dimension table created with name, scaleId, and teamId fields.
• [ ] Scale table created with name, teamId, and values fields.
  • [ ] values can be enum field. each enum value may be a tuple. e.g. (display_name, numerical_value). having both fields allows all scales to be used in math formulas, such as median or more complicated user-defined computations.
• [ ] Template table created (or ReflectTemplate renamed to be generic) with name, teamId, scope fields.
  • [ ] scope can be enum field, with values team (default), org, or public, to define who can see and use the template.
• [ ] TemplateDimension table created with templateId and dimensionId fields to model m2m relation btwn Template and Dimension table.
• [ ] Meeting table has templateId field.
• [ ] Indices on id fk fields on all tables above, to be added at discretion.

Out of Scope for v1

• Backend for "formulas" to calculate weighted scores from multiple dimensions

Other considerations

• We can hard code default dimensions, scales, templates, etc. Then clone them for each team. Similar to current behavior for retro templates. Have we encountered any limitations to this approach? How does it compare with keeping default parabol-owned dimensions, scales, and templates in the db along with team-owned ones?

Estimated effort: 8 hours

[mattkrick]

My .02 of an ER model:

• Team:SprintTemplate is 1:M
• SprintTemplate:Dimension is 1:M
• Dimension:Scale is 1:1
• Scale:ScaleValue is 1:M

if we avoid sharing 1 dimension across multiple sprint templates, we can get rid of that nasty M:N & simplify a bit. Putting a numeric value on the scale value is difficult because that'd add some complex front-end complexity (ie is a XL tshirt 2x as big as a L or 10x?), so i think we can just have the label & then that'll force us to not assume that we can do linear or geometric averages.

Since Dimension:Scale is 1:1 & the ScaleValue is a single value, we can probably denormalize to a single array on a scale object.

so, that leaves us with 3 new entities:

interface SprintTemplate {
  id: string
  teamId: string
  orgId: string // denormalized here for easy sharing at the Organization scope
  name: string
  isActive: boolean
  createdAt: Date
  lastUsedAt: Date
  updatedAt: Date
  scope: 'TEAM' | 'ORGANIZATION' | 'PUBLIC'

}

interface SprintScoreDimension {
  id: string
  sprintTemplateId: string
  createdAt: Date
  updatedAt: Date
  isActive: boolean
  name: string
  scaleId: string
  teamId: string
}

interface SprintScale {
  id: string
  createdAt: Date
  updatedAt: Date
  isActive: boolean
  teamId: string
  name: string
  units: string // or range. are these hours, days, weeks, points?
  values: string[]
}

this certainly isn't the only way forward, feel free to push back on these & keep the convo going!

[mattkrick]

We can hard code default dimensions, scales, templates, etc. Then clone them for each team. Similar to current behavior for retro templates. Have we encountered any limitations to this approach? How does it compare with keeping default parabol-owned dimensions, scales, and templates in the db along with team-owned ones?

This is going away within the next week. There were a couple limitations with it:

• 90% of templates have never been used, so it's a little wasteful
• It's pretty bad for analytics because we can't easily see which templates are popular because they're all discrete.

Your inclination is 100% correct. Soon we'll have scoped templates where our defaults will be included in the "Public" templates. If folks want to use them as is, they can. Or, they can clone them & edit them. that will reduce waste & give us some good analytics.

[tiffanyhan]

if we avoid sharing 1 dimension across multiple sprint templates, we can get rid of that nasty M:N & simplify a bit.

Yeah after I wrote the AC I realized editing a dimension would be messy and result in having stuff change from under you inadvertently. I was envisioning having dimensions auto populate or something, but that can be achieved in other ways. Dimensions are created in the scope of a template, and should be modeled that way. They're not first class citizens in and of themselves.

Putting a numeric value on the scale value is difficult because that'd add some complex front-end complexity (ie is a XL tshirt 2x as big as a L or 10x?), so i think we can just have the label & then that'll force us to not assume that we can do linear or geometric averages.

My thought here is that we still might need this down the line. I personally have never seen value of computed metrics, but then again I'm not a scrum master. Product-wise it seems like something we should have, if for nothing else than to help users feel comfortable and trust the product more. I'm comfortable with keeping it simple for now, shipping and then seeing if users start asking about metrics.

Since Dimension:Scale is 1:1 & the ScaleValue is a single value, we can probably denormalize to a single array on a scale object.

Yep, agreed.

Your inclination is 100% correct. Soon we'll have scoped templates where our defaults will be included in the "Public" templates. If folks want to use them as is, they can. Or, they can clone them & edit them. that will reduce waste & give us some good analytics.

Cool, excited to see the new templating model!

[tiffanyhan]

@mattkrick a couple questions:

• Why have a specific SprintTemplate table? It seems like ReflectTemplate and SprintTemplate would be nearly identical of the data level.
• What would the isActive field signify on the various tables?

[mattkrick]

My thought here is that we still might need this down the line. I personally have never seen value of computed metrics, but then again I'm not a scrum master.

Agreed, this is something I was thinking a bunch about, but how we convey that an average might not be arithmetical would be really hard to do. We can still calculate outliers & the mode, which from the user testing seemed like the only things folks used, anyways.

Why have a specific SprintTemplate table? It seems like ReflectTemplate and SprintTemplate would be nearly identical of the data level.

Yeah, they seem identical. Whether we combine them or we don't, it's just a gamble. In the future, let's imagine we have added 5 more meeting types, each with their own template. Do all those templates have the same fields? If so, we win! If not, we may refactor ReflectTemplate into a BaseMeetingTemplate and then new meeting templates with their own proprietary fields are subclassed from that like DecisionMeetingTemplate. Even so, that case doesn't seem terrible. Maybe we add a templateType field so we immediately know what kind of meeting it goes with?

What would the isActive field signify on the various tables?

As a rule of thumb, I'm not a fan of deleting things from DBs. It makes troubleshooting a nightmare (e.g. why doesn't the thing exist? did it ever exist? when? for whom?). Instead, I just put isActive on everything & then when it's deleted, I set isActive = false. For compliance or space concerns, we can always go through & clean up those inactive rows.

[tiffanyhan]

Yeah, they seem identical. Whether we combine them or we don't, it's just a gamble. In the future, let's imagine we have added 5 more meeting types, each with their own template. Do all those templates have the same fields? If so, we win! If not, we may refactor ReflectTemplate into a BaseMeetingTemplate and then new meeting templates with their own proprietary fields are subclassed from that like DecisionMeetingTemplate. Even so, that case doesn't seem terrible. Maybe we add a templateType field so we immediately know what kind of meeting it goes with?

Yeah fair enough. I imagined it would be synonymous to NewMeeting table with meetingType field,RetrospectiveMeeting and ActionMeeting being in app logic.

As a rule of thumb, I'm not a fan of deleting things from DBs. It makes troubleshooting a nightmare (e.g. why doesn't the thing exist? did it ever exist? when? for whom?). Instead, I just put isActive on everything & then when it's deleted, I set isActive = false. For compliance or space concerns, we can always go through & clean up those inactive rows.

Would you be against naming the field deleted to indicate a soft delete? At first glance, I assume that isActive means something like inactive field on User table, indicating whether the entity has gone awol or not.

[mattkrick]

yeah, deleted works. deletedAt as a Date might be even better if we ever need to go sleuthing 🕵️