As an SQL consultant, I often work with others to create new database models from scratch or modify existing schemas. Aside from the technical aspects, collaborations on database models can be tricky. Moving from environment to environment poses its own challenges: security restrictions sometimes prevent direct access to SQL instances, and technical resources from across the globe can be difficult to seamlessly integrate.
Deterrents these may be, but they are not insurmountable. By abstracting the modelling effort into an online system-independent tool, the focus can shift from fixing problems to developing a better database.
I recently worked on a project with a colleague over in Europe using Vertabelo, an online database design tool. In this article, I’ll detail what I found to be the biggest problems with remote collaboration as well as how using an online resource enhanced the modelling process.
The Biggest Challenges: Communication and Coordination
Even among highly skilled developers, communication and coordination can be major roadblocks to delivering a final product with consistent design elements. Especially during the initial database design, the underlying definition can change often – sometimes during other tasks – and those changes are forgotten. Developers may have their own local SQL instances, leading to inconsistencies between database designs.
However, with an online tool, communication and coordination are much easier. Knowing that we could keep our ideas organized and in one place took away some of the uncertainty that accompanies these kinds of team efforts. This left us plenty of opportunities to appreciate the actual benefits of working together virtually.
Benefit #1: Zero In-Office Configuration Hassles
The first plus – and the easiest to overlook – is the fact that there’s no onsite installation and configuration to manage.
In previous group modelling projects, I’ve always run across setup and configuration snags. Tools like ERWin, Toad, and Oracle Data Modeler all suffer from the same problem: you need to put them somewhere, either in one of the client’s servers or in one of your own. Typically, there are connection limits or VPN requirements. At some point, virtually without fail, someone winds up without access.
Since my colleague and I both have individual accounts and only share the model, we didn’t run into any access problems. And no one had to bother with a local setup and installation process, so we all saved time (and our sanity).
Benefit #2: Easy In-Project Collaboration
Using Vertabelo’s in-project system, I was able to keep all communication where it needed to be: directly within the model. I was also able to leave my colleague simple, concise love letters. (She never replied.)
When multiple people are making changes, it’s possible to look back into recent history to see if you happen to be stepping on each other. This isn’t intended as a detailed log, but rather a temporary snapshot of who has accessed the model over the last week.
Benefit #3: Problems Stay Visible
Our recipe for success in this case was simple: my colleague set up the initial model, and I went in afterwards and made changes. Had there been any errors, I would have seen them and been able to correct them:
This is another way communication is easier with an online tool – if someone in the group is having trouble, another member can jump in and immediately see the current problems.
Very little discussion outside the scope of the model is required. Between notes and clear error or warning messages, communication is largely handled through the online tool.
Benefit #4: Fine-Tuned Controls
Once we’d gotten to the point of sharing the model with the outside world, using Vertabelo’s ‘Share Model’ option allowed us to send a read-only copy to business team members:
Using role-based sharing, the non-dev-team parties can have up-to-date access to the database model with an appropriate level of permissions. Developers actively working on the project can edit the model and save changes in real-time. Other resources (business analysts, project managers) can be assigned the ‘Viewer’ role. In this role, they can keep up with the progress, but they can’t make any changes. (Note: The ‘Owner’ role should be reserved for the individual in charge of the administration of the model itself.)
The ‘Model Preview’ function can also be accomplished easily with a public link, like so:
You can send this link to your co-workers or clients to let them preview the model. Moreover, in addition to the public link you also get the code that you can embed directly on your website to display the model there – ideal for any developer who needs to take a quick look at the current version of the model.
Sharing a static copy
Not all situations warrant real-time access to the current model. For client deliverables, a static copy of the model is preferable – it is rarely appropriate or necessary for a client to have access to ongoing development work.
Sharing with the developers’ team
For delivery to a technical team, an SQL script or XML payload can be used.
Alternatively, an API is provided for retrieving the latest model (or a specific version). Developers can configure this to automatically pull down to a local machine and deploy to a SQL instance, further reducing the overhead of model distribution.
Why I’m Staying In the Cloud
I suppose you can call me lazy: I’ve decided I don’t want to waste time on lengthy installs, problematic communication gaps, and hunting down errors when I next work on a remote project. I’ll stick with the easy-to-use cloud-based system, with its seamless collaboration, centralized model, and ease of communication, and save my brainpower for its main job: database development.