Database documentation

[ECEK]

Does anybody have any examples of how you would create a report or document a database be it your own or trying to understand sombody elses.

I was recently asked to compile documentation for several databases and wondered if there was a set structure or best practice in doing this. I was also looking for ideas. Whilst I am quite capable of creating my own their might be some tips out there.

 

[Uncle Gizmo]

There's a documentation function in Access, might get you started.

 

[ECEK]

Thanks Gizmo. The documentation facility is a great tool however i was looking for something a little more user friendly. Something that somebody had written themselves with pictures and references. A PDF of how their database works ?

 

[Uncle Gizmo]

I don't use this tool myself but I have heard it is very good. Not sure if it does reporting but again I imagine it will. http://www.fmsinc.com/toplevel/about.htm

 

[spikepl]

You can use the free mz tools to have a consistent framework for in-code documentation of modules, functions and subroutines. Somebody still needs to fill it out , though!

 

[jdraw]

This may give you some background, purpose, target audience etc for database documentation.

I also found this that could be applied to any database (nothing specific to RIPE in my view). The approach should give you the headings/topics and some indication of the level of info involved..

I also found this ad during my google search. I have never heard of this product, but it claims to do "documentation" for Access databases and others. It offers a free trial and shows a cost of $179. The trial may give you some idea of this product's relevance to your needs.

What types of databases are involved? Mission critical?
What categories of users do you have?
Do you have specifications for the databases you have?
Do you have a means to identify any changes that have been made, or have been requested, of existing databases?
Do you have any data management function within your organization? Project planning, priorities, feasibility studies, analysis , design, development, testing, change requests, project management, approvals, resource allocation,
operational procedures, maintenance procedures, backup routines........
Technical details re common functions, naming conventions, data models....

Really need more info and context to provide more focused response.

 

[The_Doc_Man]

Documentation comes in multiple flavors. You can easily get the Documenter function to build out lists of table contents. You can get all sorts of other things like dependency lists, relationship diagrams, and export of queries in SQL view. There are ways to diagram controls using the Documenter.

The down-side is that this is half useless in understanding the database - because there is another half to consider. The question to be resolved usually comes in some sort of "Theory of Operation" document.

For example, how many data sources do you have? How do they relate to one another? When (and why) would someone use a particular form? If you are fortunate, you can find diagrams of data flow. Some 3rd party tools will do a data-flow diagram. But the more complex your database, the less likely that one of those tools will tell you what you really wanted to know. If you are going to do one of these Theory of Ops documents, there will be no substitute for careful manual examination of what is done in the code.

Speaking of which, the code (macros and VBA parts) are worse with Access than with some programming environments because Access is event-driven and the code that is listed in some order in a module is rarely likely to actually be executed in that order. Again, if you are to have a theory document, you will need to understand not only the code but the event that triggers the code. Most likely, this will be manual rather than automatic. (At least, I have not yet seen a really good code analyzer that can tell you WHY something is done.)

 

[Rx_]

The tool mentioned above is a good start. Don't expect it to automate the process. It will still take many hours a week on your part to use it and keep it maintained. It will just take less hours than if you don't have a tool.
If management scoffs at $179, then they are probably un-realistic about the task to begin with.
Well, those of us Old School developers have seen this change around over the decades. During the Cold War, I installed a moving (automated) Tape Drive library for around 800 DB / Code developers. Under the contract, we hired three PhD in Library Science to document and categorize code for version control.
Later years, I was QA manager for a project with 100 DB / Code developers for IBM, SQL Server (1.0), Access, VB 3-6, C++. We had three DBA /Programmers for documentation.
Then later for the EPA, I got contracts to hire Documentation experts just to translate the EPA's DB documentation.
Now days, it is just the Nike advertisement. "Just do it"
This weekend, I talked to a $35 an hour contractor that was hired to document DB/Code/Network configurations. They created some great templates. But, the engineers would not provide any input since it wasn't in their job description.
The NYC V.P. fired the documentation person because he expected the documentation person to just figure it out. The company is a Major consulting firm. The V.P. rotate in / out to other major consulting firms every two years on average taking the big bonus with them.
The trend is just getting worse and worse as there is less responsibility at the top levels to actually have a written plan for documentation. i.e. the documentation process is it self undocumented.

My advice is that if any programmer is expected to help document the process "in their spare time", get out of that position. It is set up for failure.
The tool is a great start. However, it still takes time to set it up and maintain.
This is a great task opportunity for a College Internship or a New Hire.
Giving a New Hire the chance to identify the DB Structure, table names, field names, relationships, server locations, and business code has a great return on investment.
This process is still used in the US Flagship Maritime Union for Internships and 3rd Class Engineers. Thanks to traditions, it has been found to work well and somewhat escape the new Nike Style Management.

Please be sure to let us know how this works out for you. Many of us are starving for a modern success story.