How should I document a database schema?

Question

I am going to be writing some user-facing documentation for a database that visitors can query. That is, the people writing queries are not the ones who created the database; they can come in, look at what the database offers, and write their SQL queries. Think of something like SEDE.

I could just give people the database schema, but that only tells you so much. For example, if a value is an integer but it's an enumeration (1 means this, 2 means that, 3 means this other thing, etc), you can't get that from the schema alone. So I need to produce some documentation for the tables and columns in the database, which also provides me the opportunity to present the information in whatever organizational structure makes the most sense for our users.

My question is: are there best practices for going from a DB schema to documentation? If this were an API, for example, then in some languages I could write inline comments that could be automatically turned into reference documentation using tools like Javadoc or Doxygen. Is there something like that for DB schemas, or would I have to write my own transformation tools (which isn't worth the effort for the size of this project)? Or do people writing schemas just inspect the schema and write separate documentation? Are there conventions for doing this? I'm an experienced technical writer but I'm new to databases.

(In case it matters, I need to deliver HTML. This is a volunteer project (not actually open-source, but along those lines) and I have no budget for tools.)

Answer 1

If this is user-facing documentation, then make up a data dictionary that describes the tables and columns with supplementary blurbs about the meaning of the data (e.g. the meanings of specific values in a column). This can be a straightforward HTML document with the supplementary descriptions as text.

If you need to produce E/R diagrams then Visio professional (version 2010 and earlier) has a passably good database diagramming feature and should be fairly widely available. There are other tools that will do this as well. Unfortunately Visio's SVG output is pretty crap and I'm not aware of any modelling tool that will produce good SVG diagrams.

Dia is an open-source diagramming tool and has a UML modeller which could be co-opted to produce usable E-R diagrams that will render in SVG. If you can live with bitmap illustrations of the data model then you can get away with taking screenshots from Visio. In this case, split the model into subsystems and make illustrations of the parts.

For user documentation this will be far more useful than any model-based approach.

Answer 2

It depends on your audience.

Blessed Geek suggested UML enumeration, but this assumes that the users understand UML. Your question shows that you know technology well enough to know the capabilities of your users.

Writers often use Javadoc or Doxygen to generate API documentation because those tools easily generate new docs when the API itself changes. Updating the documentation as the code changes is useful when the code is changing a lot. For your database schema, however, that may not be a consideration. Schemas don't change very often, and when they do, they're usually added to. The technical impact of deleting or modifying fields in a database is large.

It's easy enough to print out field names and types from a database using its tools. From this printout, you can produce an HTML table that has a blank column where you can provide more documentation about the field. An image that's something like a simplified UML diagram can help readers understand how the tables of the database are related to each other. If you want some additional ideas, take a look at Contacts Provider in the Android documentation.