Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users
Tabletop RPGs
Tabletop RPGs
Community Proposals
Community Proposals
tag:snake search within a tag
answers:0 unanswered questions
user:xxxx search by author id
score:0.5 posts with 0.5+ score
"snake oil" exact phrase
votes:4 posts with 4+ votes
created:<1w created < 1 week ago
post_type:xxxx type of post
Search help
Notifications
Mark all as read See all your notifications »
Q&A

Post History

60%
+1 −0
Q&A How should I document a database schema?

While the other answers already mention tools to create graphical presentations of your tables / columns and ways to add short information to that, for example by creating an HTML table with an emp...

posted 5y ago by Secespitus‭

Answer
#1: Initial revision by user avatar Secespitus‭ · 2019-12-17T20:41:17Z (almost 5 years ago)
While the other answers already mention tools to create graphical presentations of your tables / columns and ways to add short information to that, for example by creating an HTML table with an empty column that you can fill with additional information, it's important to remember what people are looking for when viewing that documentation. You already are concerned about people not knowing certain implicit restrictions in the data, such as the integer that is basically just a key in an enumeration as in your example. 

Sometimes it might be better to create a dimension table that can be joined with the table containing such data to give the users a better idea about what the data is while directly viewing the results of their queries. But it might not always be possible to add more tables / columns to the database. Many databases allow comments to be added to columns, tables and other objects, which might be a good starting point for your documentation and for extracting the most relevant parts because these can simply be queried. Programmers are sometimes known not to take comments too seriously though, which means that you might need to come up with the descriptions yourself. 

And to be honest, there is no right approach in such a situation. You mentioned that you need to produce HTML in the end so you should focus on creating webpages that make it easy to find what you are looking for by dividing it into appropriate chapters, such as a general introduction and more detailed pages for individual categories. The same could be said about a Microsoft Word document or something similar. The final representation might be different, but for such documentation that is directed at technical folks to understand how to write database queries you will mostly wind up with similar results - whether you have a big document in a central place such as SharePoint with lots of different levels of headings you can jump to or a webpage that you can view in your browser with an overview page from where you can go to each "chapter" doesn't really make a difference. A webpage has quite a view advantages though, especially with larger pictures and in cases where you want to show tables with lots of columns. If the database you are working on is similar to SEDE you might even want to link example queries that the user can just try and fork for themselves later. 

While one part of the site could be dedicated to graphical presentations of the data and making it easy to see how the available tables belong together, another part might illustrate what tables are available, or example via a simple search. You could add a short "management summary" style comment to each table that mentions what the table is about and whether there is something to be aware of in case someone wants to look at the data. From there you can link to specific pages for each table which contains information about its columns, again with a short description. In cases where there are hidden "gotchas" such as the one you mention you should mark those tables and columns on their respective pages in a way that shows your readers that they should take a closer look and link to a detailed page describing the problems. 

Furthermore you should have a page dedicated to such "gotchas" where you describe in prose what the problems with some of the data or datastructures are and link to the respective detail pages. This could for example be done in an order that shows how problematic something is. Make it easy to find and easy to see at first glance that there are some things that you need to be careful about when working with the database. 

I once encountered a database that I couldn't change and needed to document some things for others to read up on the database design. Nobody wanted to touch the thing and I soon understood why. There were... a couple problems with the design. For example I couldn't create a userful graphical presentation of how the tables were connected from the database because while the database allowed people to use things such as Foreign Keys to link tables the designers of this specific application opted for not using such contrived things - instead there was a column in some of the tables that listed Tuples of Table Name, Column Name and Values. That needed a lot of prose to describe to others how they could hope to query the database with expansive examples for the most difficult and important things that you might want to know, as well as common errors that happened while creating queries. 

Most of the time a picture is worth a thousand words - but sometimes you need a thousand words because nobody would understand the picture.