I keep running into a problem at work, and I'm hoping that others know a solution either from personal experience or from best practices.

I am not a programmer. I do, however, heavily use RDBMS tables which are populated by code that our programmers write. I also use other products of our developers'. Most often, these products are not documented.

Here's an example (genericized): We have a table that indicates status changes to accounts. I thought it included every time someone attempts the change; turns out, it includes only successful changes, and excludes attempts to change to the status currently held. This greatly affects my queries against that table. But no one had documented it anywhere.

Another example: A JIRA feature-request requested that some cron affect accounts after n days of the accounts' inactivity. Does that mean 24×n hours exactly? Does it mean the difference in dates between the last activity and the cron should be n? Something else, perhaps? Again, the precise definition here affects my queries against the relevant table, but was undocumented.

In both those cases, I had to sit down with the relevant developer, who read through a bunch of code, some of which she herself had written, to determine what the code did. This is a waste of time for the developer, who will have to sit down with another employee a few months later when he/she has the same question and the programmer doesn't remember the answer. And then again a few months later.

What should we do about this? There seems to be some sort of documentation needed. Where should it be? Or is the current situation ideal?

Some ideas I thought of:

  • Comment RDBMS tables and columns. (The RDBMS we use allows for this.) Or have separate documentation broken down by RDBMS table. But a developer here correctly told me that if when a developer fails to document something, people will be relying on outdated documentation, which is worse than the current situation. Also, this would only document the RDBMS; there are, of course, other products of the developers'.
  • In any JIRA issue (the vast majority, perhaps all, of our code changes are in JIRA), include the RDBMS table name (and/or other important search terms) and documentation of what was done. That way, people wanting to know about any developers' product have a central location to search, and can read each diff's documentation.

Does anyone have a solution? What do other companies do? What have you seen, or what best practices exist?

  • Possible duplicate of Should I comment Tables or Columns in my database? – gnat Dec 21 '17 at 18:09
  • 1
    You are way too close to the internals of the program. As an end user, there is no need for you to know the name of the table or the column names. Why are you querying the tables directly? Isn't there help or user manual to help you use the software that is being developed? – Jon Raynor Dec 21 '17 at 18:34
  • 2
    @JonRaynor, I think it's pretty standard that non-programmers use SQL. Or at least data analysts like myself. So, yes, I need to know the names of tables and columns. That's not the "internals of the program", as you put it, but "products of our developers'", as I did. – msh210 Dec 21 '17 at 18:57
  • @gnat, I don't see how that's the same at all. That offers the practice of commenting tables and asks whether it should be done (whether for reasons I mention or not). This offers a problem and asks for its solution (which may or may not be commenting tables). – msh210 Dec 21 '17 at 19:03

In general, the closer documentation is to the code that it documents, the more likely it is to stay correct over time. This is one reason that auto-generated reference documentation like Javadoc and Doxygen works better than externally-written reference documentation: the usage syntax that generated documentation shows is by definition always correct (drawn straight from the code), and the comments are right next to the declarations in the code so there's a better chance that somebody editing the code to change the interface will see and (we hope) update the comment.

If your database supports commenting tables and columns, see if there is a way to auto-generate reference documentation from those comments. Generated documentation is easier to browse and search than repeatedly doing the SQL equivalent of "man cmd" one table or column at a time. (I have no experience with auto-generating documentation from comments in databases, so I can't suggest tools. See if the documentation for the database platform you're using says anything about that, where it talks about how to add comments to tables/columns.)

Just generating documentation from comments (or accessing the comments from the command line) is unlikely to solve your whole problem. We see this with APIs too; the reference documentation tells you how each class, function, etc works individually, and might have some overview documentation added in, but it usually doesn't give you higher-level information about intended usage, conceptual information, well-documented examples, and so on. Further, you've said you need to document things that are not table and column definitions.

I therefore suggest a hybrid approach: generate reference documentation from the comments if at all possible, and create a place where you can jointly create and curate other documentation as needs arise. A wiki works well for this (and is searchable). External documentation is inherently less reliable than the actual code, so you'll need the will to create it and keep it up to date. This works better in low-volatility projects; if your interfaces are changing every day, external documentation is premature.

If you need to document tables externally instead of deriving documentation from the code and comments, a format my team has found helpful is the following. For each table, write a paragraph or two about what the table is for and (if relevant) how it relates to other tables. If there are restrictions on usage (like privileges), include that too. Then create a table with three columns: column name, data type, description.

Finally, in the absence of documentation, I sometimes find answers to how something really works in test code. (A comment also mentions this.) Do your developers have test suites? Are the tests (including SQL queries) and expected output accessible? If so, see if you find your answers there if you don't want to bother a developer -- and then use what you learned to improve your internal documentation, wherever you're keeping it.

  • Interesting ideas; many thanks; I'll have to look into auto-generated reference documentation, which I didn't know (but shoulda guessed) exists. Our code is volatile (though of course I didn't mention that in the question and this answer will, I trust, help future readers also). One thing: can you please clarify what you mean by "(code) interface"? – msh210 Dec 21 '17 at 19:24
  • @msh210 I clarified in an edit; sorry about the jargon. – Monica Cellio Dec 21 '17 at 20:17
  • Code changes that don't have complete documentation for whatever autogeneration system is in place shouldn't be checked in. What can be good, if people are willing to use it, is autogenerated code with comments where users and developers can add examples and clarifications. Also tests are incredibly helpful as documentation. The test of the n days problem would tell you how n days was defined. – Elin Dec 21 '17 at 21:12

I think the problem is that you are expecting a scientific level of defined functionality from a product which isn't developed to that standard.

Some programs are specced and developed to very high tolerances. In those cases you can expect the documentation to state exactly what the program does. For example compilers, implementations of mathematical functions, drivers for hardware etc

But for most line-of-business applications the specifications are much looser, and consequently the functionality is undefined once you drill down below what was originally requested.

Your JIRA example is a good one. 'The account must change after n days' is a pretty specific spec if you are sales person or even a laywer. They would chafe if the developer came back asking about daylight savings and leap seconds.

If you come back later and ask the same question, the answer is 'undefined' it might do x or it might do y. It's a blunt tool.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.