I’m no database designer but I do occasionally need to build a very simple database model, no more than a small handful of tables, to test out some code for an article or presentation. The other day, I was indulging in my usual habit of slowly tapping out CREATE and ALTER TABLE statements in SQL, when a colleague politely alerted me to the error of my ways, as he often does. Since then, I’ve switched to designing the tables visually, using the database diagram tool in SSMS.

It is certainly a more natural and intuitive way of designing a database. It’s a little clunky to use but it’s surprising how much time it saves, being able to simply right-click to specify keys and constraints, or drag-and-drop to create the relationships. If you update the visual model, you can auto-generate a script to update the database, and conversely if you update tables or relationships directly, the model can update. The tool has a few shortcomings, and doesn’t display the exact nature of the relationships as clearly as I’d like, but for a simple model it works!

We tend to think of the database ‘source’ as a set of individual text files with the DDL for each “object”, such as is commonly stored in a version control system. This is fine in the later stages of a development project, where we can fine tune the schema by changing the CREATE statements. However, surely during the early stages the team ought to be working from the diagram, and reverse-engineering the scripts from the associated live database? During this phase, it is really the diagram that ought to be regarded as the “source of truth” for the database.

Changing source code isn’t the only way of altering databases. As well as ER diagramming tools, there are plenty of other visual tools for creating tables and queries. Several are in SSMS, originally from Access, and there are many third-party tools around. They can save a lot of time and effort, and can eliminate error. Perusing a completed visual data model, with the keys indicated clearly, lines depicting the relationships and dependencies, one immediately sees the likely consequences of a change. Some of these tools allow you to add annotations and documentation, with definitions of the tables, columns, relationships, and constraints, so that their intended use is clear to everyone.

At Simple-Talk we are about to review online data modelling tools. The only one I’ve checked out, although briefly, is Vertabelo, and it looks very promising. Crucially, it’s cloud-based and makes it very easy to share models with teammates, and work collaboratively on those models. It is tempting.

Where a design tool creates an artefact above and beyond the DDL change-script, such as a database diagram, should this be subject to version control? Is there anything about using this sort of tool that would make the database purist raise an eyebrow? Would the use of a design tool affect the CI process? As always, I’d love to know your views.