Development Tools: DB Documentation/Decision Recording

[MSPJ]

I thought it might be helpful to offer a series of posts on tools I've found helpful (or, not helpful, in some cases).  Some of these will be of use primarily for newer developers, but perhaps others will be of more general interest.  

This first post will focus on tools for documenting the development process and associated decisions.  As I've learned filemaker and developed my solutions, I've found myself looking for a  way to record my process - especially regarding the relationship graph.  I find the sticky notes woefully inadequate for what I'd like to record: why I created a table occurrence group, what scripts relate to it, what assumptions were involved, etc.  (I freely admit that this could be an indicator that my relationships are not sufficiently intuitive - perhaps others don't need additional descriptions).   Ideally, I'd also like to be better able to document changes to the graph over time.

Scripts, calculations, and CFs can certainly be more easily documented through comments - but these are still isolated.  I can open up each calculation and read the comments -but I can't in one place describe which calcs, scripts and CFs were intended to work together for a particular function.

I also wanted a better way to describe what I had done at a more overview level, and why I had decided to do something one way and not another way. This falls under the category of Architectural Knowledge Management, or Architectural Decision Recording.  And I hoped to be able to tie all these different pieces together in some reasonably intuitive fashion. 

So all that said - here's a list of what I've tried and how well they've worked for me. Hopefully this will be helpful to some folks. I'll start with what I've found most helpful and mention the less successful ones after.

1. Enterprise Architext (Sparx) - this is a multipurpose UML modelling tool .  It's quite affordable compared to most other such tools and very flexible.  The learning curve is fairly steep - though if you have a lot of UML experience, you'll just need to learn the quirks of EA.  

You can create conceptual, logical and physical entity relationship diagrams or do database modelling, and re-use tables. 

Initially, I tried to recreate my structures in some detail, entering columns (fields), and creating specific connections.  This ended up being much too time-consuming, and it was hard to model the idea of table occurrences effectively.  

I found it more helpful to create diagrams that documented key elements of relationship groups at a more conceptual level.  EA allows many ways to add documentation - free floating notes, notes attached to elements, detailed documentation within elements, configurable tags and stereotypes, or connected external documents.

It also allows creating maintenance elements for tasks, defects, issues and many others.  An addin allows integration with Trac.  (I initially used EA for bug tracking along with Trac – but I’ve now switched to Target Process, as described in another post). It is also an excellent tool for requirements, features, user stories etc – which can be tied to other elements (such as models of relationships). EA also supports a variety of approaches to version control.

In addition, there are free addons to  EA that allow more sophisticated architectural issue logging and decision recording, allowing for structured logging of problems, context, options considered and reasons for the chosen solution. Here are links for two of them. The decision architect add-in is no longer updated and does not work perfectly with the current version of EA but I’ve still had some luck with it.

https://github.com/IFS-HSR/ADMentor/releases

https://community.sparxsystems.com/white-papers/879-documenting-software-architecture-in-enterprise-architect

The primary disadvantage of EA is effort – you need to invest time in learning it, configuring it to match your process and needs, and then maintaining it with enough discipline to remain useful and up to date.

The big advantage of EA – if you have the time and patience – is the ability to use one tool to model and link user stories, use cases, features, requirements, releases, database structures, decisions and considerations, issues, defects, tasks linked to elements, change history, and more. I’ve also pasted in some calculations that required more explanation and scripts (you need to find a way to get the script into a pure ascii version that will be readable – I think I used FM Perception as an intermediary. Again  - a lot of work.)

If you need to generate formal documentation, again EA is incredibly powerful – and very time-consuming to master.

2. OneNote + SnagIT + ADR Templates

A less structured but less time-consuming approach would be using a free-form tool like OneNote with some help.  Rather than trying to recreate models of key elements of the relationship graph, one could grab screenshots of TOGs from the relationship graph and annotate them directly with something like SnagIt.  Those could be then pasted into OneNote, which also offers simple tagging and excellent search.  One could use sections and sections groups to document the graph, and another section to document architectural decisions (which could be hyperlinked to the TOG diagrams within OneNote).  There are many templates available for those looking for a little more structure in their decision recording. 

See here for a good source:

https://github.com/joelparkerhenderson/architecture_decision_record#how-to-start-using-adrs-with-tools

If you use Git, there are also some Git tools for this purpose.

Up until now, I’ve relied heavily on Enterprise Architect, but as I’ve moved a lot into Target Process (bugs, requests, features, user stories, tasks, release planning), there may be less benefit to EA- as such I may consider this lower tech approach, especially if I can find good ways to link back to entities in Target Process. TP also links to my SVN repository which is also helpful.  But there's no good way to model the structure in TP (nor is it intended for that purpose), so it's only one part of the package.

3.  Visio 

Visio is of course well-known.  I initially tried using it – but found it was too limited and didn’t have enough power to justify the effort.  EA is more work but vastly more powerful.  You can certainly do conceptual relationship models in Visio, but EA offers far more options and flexibility. You won’t outgrow EA, but you well might Visio.

4. SQL (and other) DB Engineering Tools

There are dozens of tools out there such as MySQL workbench, Valentina Studio, and many others. I tried a bunch.  Some were able to connect to my FMP database through ODBC or JDBC, but FMI has not allowed the relationship graph to be fully accessible to these tools. DBSchema claimed filemaker compatibility, but at best, I was able to pull in the tables and fields from filemaker – but no relationships or keys. In the end, that wasn’t very helpful, and it didn’t allow updating over time without losing work I’d done to document things. In my experience, these tools don’t offer enough if you can’t actually fully connect to the database. A lot of effort for very little gain.  May as well stick with FMP's sticky notes on the graph. 

5.  DDR

Of course, the DDR generated by Filemaker could be used to document the database – that’s what it’s for.  But there’s no way to add your own notes to it and maintain those the next time you generate a new DDR. 

6.  Base Elements

I had high hopes for using Base Elements for this purpose.  After all, it can show you every aspect of your database from about any perspective.  And it even has fields for notes.  But so far, there is no way to maintain those notes from one version to the next of your solution or of Base Elements itself. Currently, the notes feature in BE is more useful for making notes on desired changes or defects than for long-term documentation. (I’ve spoken to Nick at Goya about this and he appreciates the value of this capability, so perhaps it will be added in the future).

 7. Dataedo

This tool has some promise but is not quite there. Similar to the various SQL engineering tools it can connect to databases and extract structure and relationships.  However, it is designed primarily for the purpose of documentation rather than as a database engineering tool, and as such seems to offer more power for this purpose. In particular, as databases change over time it does not wipe out existing documentation.  It does not connect well to FMP either -but the developers have expressed an interest in exploring using the DDR to derive structure, while still maintaining continuity of the model.  It remains to be seen if they’ll get there, but it’s worth watching.

Please let me know if this is at all helpful.

Michael 

 

 

 

Edited April 1, 2019 by MSPJ
Correct mistake

[Fitch]

Interesting roundup, Michael. Thank for taking the time to write this up.

[OlgerDiekstra]

Instead of Enterprise Architect or Visio, there's also draw.io, which is free and can do pretty much all of it too. (https://draw.io).

Here's a blog post discussing UML in draw.io: https://about.draw.io/uml-class-diagrams-in-draw-io/

Draw.io is my go to tool for any diagramming. As my role is not just FileMaker development but also general IT, for instance I create interactive (layered) floorplans with draw.io that I can load in a browser and use for day to day management. All my printers are located on it, all computers, location of security cameras, etc. I can click on a printer and it will take me to its webpage.

draw.io stores its diagrams in xml files, but there are heaps of import and export options.

[MSPJ]

Fitch - happy to give something small back, I've gained so much from these forums.

Also I've been jealous of all the developer tools available for conventional languages, so I'm always on the lookout for these kinds of things. 

If FMI would offer either a true ide for scripts, calcs, etc or an easier way to interface, or even go back and forth to pure text, we could use merge and branch tools. I'll do another post on what I've come up with in that arena.

Olger - I remember trying draw.io, as well as a few other web-based apps. Don't remember now why i rejected it. Is there a subscription cost? I still prefer desktop software when it's a good option.

[OlgerDiekstra]

21 hours ago, MSPJ said:

Olger - I remember trying draw.io, as well as a few other web-based apps. Don't remember now why i rejected it. Is there a subscription cost? I still prefer desktop software when it's a good option.

Draw.io is free. The web app can be used offline. Alternatively, there's a desktop app (no doubt the webapp wrapped into something). It's extremely good value for money I think. The thing that I like is that I no longer need to have so many apps installed. Things like draw.io tools, I can use wherever I like just by opening a browser. I've never compared it to Visio (done a lot in Visio over the years) but I've never come across anything that I couldn't do in draw.io.