I’m pretty deep into designing a new system for the org I work for. Before I invest a ton more time in it, I wanted to run the schema by people to see if they think it makes sense, or if there’s a smarter way to do this.
The process is organizations going through an accreditation process. First they register their interest, then they get screened. If they pass the screening they are then reviewed by a peer review panel and finally a decision is made on their application - either for resubmission, a conditional acceptance or total acceptance. Each step has many steps within it, and the process in total can take 6 months-2 years. After they become accredited they must submit an annual compliance report then apply to be reaccredit every 5 years.
This process is currently tracked through a huge number of spreadsheets, none of which connect to each other. There’s a lot of manual copy/pasting, and a lack of data hygeine around how things are formatted.
Enter Coda, of course. I’m moving this all into a single Coda doc. The way I am setting it up is to make two key tables - Organisations & Audits. An Audit type is either pre-screening, screening, peer review, and compliance report. At the start I decided that each audit process would be its own row in the audit table - and have a button once that audit is complete to update the status and create a new row in the audit table for the next phase. The audit table has the fields for all of these audit types, and I’m making a section for each audit type with irrelevant rows hidden. So, when the screening is complete, this button updates the status to “screened” then creates a new row in the Audit table as a “Review” type audit. This was designed to allow multiple reviews for the same org, either if an org fails a screening or for when they need to be screened/reviewed again during an accreditation.
Does this make sense, or am I missing something better design principle here? I plan to add tables/views to this as I integrate more job functions into the doc. I also need to figure out importing/upserting Salesforce data - either that or just exclude data already collected by Salesforce and make people go there for that.
Would appreciate any thoughts or advice!