As I work on the immediate milestone of crowdmatching system runs on autopilot, I wanted to have an idea of where the database might be headed, so I can make the now-changes in a future-compatible way. Here’s what I came up with. This schema supports multiple projects, multiple goals per project, and a flexible pledge amount from patrons.
It follows the same principle as the current DB: tables ending with History are purely records of what happened; they are never used by the system in operation[1], so they can be freely moved to cold storage if they get too large (although of course this will limit the history we can show a patron on their dashboard).
The image below shows the history: I started by naively adding a new table for each step along the way with just the data needed (colored by step!), then de-duplicated near the end when I figured out which parts could be calculated from other tables instead.
The top right is just some scratch for reminding me how the current DB is.
I think my only outstanding question is:
- How to keep track of what (email) notifications we’ve sent? Should it be in the Crowdmatch part of the DB at all?
There’s other questions on the board that I think I’ve answered, but getting a good photo of the whiteboard is surprisingly hard (best done outside) so I’m not going to bother updating it for small changes:
- Should be denormalize Crowdmatch by referencing the GoalHistory table instead?
- ⇒ No, because History tables should not be required for regular operation.
- Do we even need the GoalHistory table, given we don’t expect goals to change?
- ⇒ Yes; we still want to track additions & deletions and their dates.
Actually not true currently since we use the pledge history instead of storing some extra state. I want to fix that; this was part of figuring out how. ↩︎
