How to efficiently question the db design and get work done

[fyliu]

Overview

What is the preferred process to discuss changes to the schema design? I think we need Bonnie's historical knowledge as well as Nicole's database design expertise to settle these questions. The permission table naming is an example where Bonnie's input is valuable.

Questions about the design can come up any time, such as before, during, and even after working on a new table issue, like in PR reviews, and need to be resolved. Right now, we can only guess at the design intentions based on what's in the ERD and very limited descriptions in the spreadsheet.

Action Items

• come up with a process to question, propose, discuss, and decide on changes
• (maybe) set a meeting to go over database so we can create documentation on how it's intended to work, Bonnie, Nicole, Fang, (Azania and other devs? Cynthia?)

Some database design questions/proposed changes

• while working on the table: How is faq_viewed meant to be used? #93, from Azania.
• from a PR review Separate Phone model from User #89
• from a PR review Make current_skills a many-to-many with a new skills table #90
• from a PR review Store project logos in django #91
• while having a different discussion about the table: permission should have a brigade_id association
• while looking at the design and how to use it: Epic: Redesign event schema #88

[fyliu]

I like documentation. It allows the team to work more independently with less need and overhead of asking simple questions.
I think we need better documentation on how the database is designed.

• We can make guesses based on how the tables and fields are laid out, but sometimes there's more to the decision.
• Some things make sense at first glance but don't work when we go to implement them.

I'd like to have a recorded meeting to go over parts of the system and then write out documentation on them. Knowing the bigger picture will hopefully allow devs to understand how things are meant to work, and have more confidence to make sensible changes when the design needs small tweaks.

Then translate that to documentation on how we want the client to use them to make sure that makes sense.

[chelseybeck]

I agree that there should be a process for updating schemas and elements. It's a common practice in data engineering to start development and realize that some part of the design needs changing. We could propose changes in a PR, sheet, or during a scheduled meeting.

[fyliu]

Here's the background for why I created this issue:
Azania did work on some models and we realized sometimes the design itself is not that well thought out. I blame having a database layout without having documented use cases for how it’s meant to be used. We made good guesses based on what the table design said, and then found out the usage is completely different because one of the fields actually shouldn’t have been there. That’s the faq_viewed model. It had an extra project_id field so we thought faqs were related to projects, as in projects have their own faqs. It turned out faqs were meant to be independent of everything. So I made this issue wanting to figure out how we can eliminate this kind of misunderstandings. Initially, I was thinking of having a meeting to go over everything with Bonnie and document everything.

• How is faq_viewed meant to be used? #93

But this issue is too broad. I don't think we can and should spend time going over all the models at the same time. Maybe a better way is we should come up with use cases for the models we plan to work on and run those by Bonnie to make sure they’re reasonable before working on them. If it looks very simple, then just skip the verifying step. But some use cases would still be helpful for testing, especially if someone wanted to do TDD. We can maybe document that in a page for each model, or something else. I think we need to try a few things to figure out the best way to document it.

[ExperimentsInHonesty]

Closed as unplanned, we are going to make new issues when things come up. If you are not sure if you should make a new issue, add it to the team meeting agenda #103 or the pm agenda #28