Schema Advice - building a big accreditation system

Hi everyone,

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!

@Tim_Richardson1 this seems reasonable to start with - i think answers to below questions could help bit more in solidifying schema/ give bit more context to me(and others) .

  1. Do you want to know what all audit activity specific org went through?
  2. Do you want to know what all orgs went through a specific activity / what sort of reporting are you looking for around this?
  3. How many audit activities you are planning to have (some rough estimates around # of organizations * no of audit types * records per audit type ) can help with scale you are thinking of?
  4. Do you need archival functionality? how many years of data you need to track?
  5. how many attributes/column are common between different _audit type?
  6. How many users are in your team? is each person responsible for specific orgs? or are they responsible for specific audit type?

Thanks for using Coda for your use case and cant wait to hear more once you build and use it in your team.


Happy new year! And thanks for this response Krunal. I really appreciate you helping me with this.

  1. Yes - currently this is captured in the lookup column on the org table - it looks up all associated audits and lists them.
  2. Will need a lot of reports. Assessing how long orgs tend to spend in a given a stage in order to analyse pipeline blockages, for example. I’m new to the org so reporting needs will likely arise over time.
  3. Orgs in this system is in the hundreds, should scale to being 1000+. There are about 5-6 audit types and an org will go through about 4 audits when they join then a repeating audit process for their sustained accreditation. So an org with us for 4 years will go through about 7-8 audits. Most orgs will be in the pre-accreditation stage (pre-conversion) and so will only be in 1 or 2 audits.
  4. Archiving would be great if possible. For now I am starting fresh with just the last year of data, but it would be good to know that the schema is capable of archiving functionality going forward.
  5. Good question… it varies. Some audit types are very similar, almost identical (e.g. accreditation and reaccreditation) while others have next to nothing in common other than org name.
  6. 4-5 users. Currently everyone involved is somewhat responsible for all orgs and audit types but with different job functions around the process. So one handles invoicing and payments, another handles writing and sending letters to charities, the director would go in for an overview of progress/dashboard type stuff.

Hope this makes sense. Thanks again for offering to help with this.