What are the best practices for writing database documentation for different audiences and purposes?

I believe that documentation is a sign of a well resourced team with good capacity. Writing and maintaining documentation takes time. If a team is not well resourced then you can expect documentation to take a hit. I’d say to anyone struggling to do it all, just do what you can whenever you can. Every little helps.

One thing I have found helpful is think about the most junior person who might be using the document and see if the prepared document communicate the content clearly to them. Also, think about the most senior person who might be checking this document and see if the document keeps the required professional standard.

Automate a lot. There are only two ways to keep database and it's documentation trully in sync. 1) have one generated from another. - you can put some extra metadata inside database and use that to generate documentation (easy, but may limit documentation) - or make your documentation runnable, so it will modify database schema to match (much harder, but documentation will be more human). 2) alternatively, have an requirement, enforced by your change management system, that any changes that modify your database will be automatically rejected unless they include documentation changes - you can automatically check that documentation is changed, and leave to human reviewer decide if proposed database changes match documentation changes.

If the document is relevant, its always a best practice to keep a periodic review to see if the documents are updated properly. This will give more confidence for the users of the document to rely on them.

Review documentation every time making changes to database itself. Also when monitoring reports that data usage pattern has changed - it might be good to re-review documentation - you maybe will get playbook what to do with such pattern and if it happened before, or documentation will help you to decide if it is time to think about data model changes as well.

One commit at a time. Changing both database and documentation. First it will slow you down, but eventually it will increase velocity for whole company. This is the way.

DONT 1... Write a wall of text "thesis" length brain dump of what you know, and expect others to absorb that information like a MENSA challenge. Documentation is for sharing, not showing off. DO 1... DIAGRAMS.If you need to reference your environment to explain a process or system, have a diagram of that environment. 2. Workflows - Diagramise your work flow. Dont describe it. For each section of your workflow, create an index that can be referenced off-page to discuss or describe that part of the workflow in more detail. 3. Seperate human process workflows (actions and decisions) from system workflows (data flow and sequence of code driven events). Each is different, but each can be overlaid on top of the other for context.

Generate and automate a lot. Remember to not repeat yourself. Think of the future readers, you need documentation that is short and to the point. Keep both "why we do this and that" (architecture decisions, and ways to challenge them in the future) and "what to do when..." (incident response playboks) extra easy to find. That will make life easier for stakeholders and improve whole system in general.

In my opinion revision control of the baseline and all associated changes against that baseline is imperative to documentation control.