• Eric M Russell - Friday, November 30, 2018 7:27 AM

    Database modeling tools can be expensive, complicated, and despite all their bells and whistles, are ultimately rigid. There have been situations where I have questioned why a database was designed a certain way, things like inappropriate choice of datatypes or use of triggers, and the developer told me that's just how XYZ tool does it. These tools also require a 500mb download to be installed on your PC just to view and edit the project.

    However, if what you're really wanting to do is create some basic entity relationship documentation for an existing database and then easily share that documentation with others, then there are some online tools that are either free or affordable. They are easy to use and have all the functionality you'd need for your purpose without all the fuss. Robert Sheldon wrote up a review on several of them a couple of years ago.
    https://www.red-gate.com/simple-talk/sql/database-administration/five-online-database-modelling-services/

    This is true, of course. It took me forever to find an ERD tool that worked the way I wanted to, and it took a few weeks to really learn it.

    HOWEVER.

    ModelRight (the one I really like) is a bit pricey. For the SQL-Server only version it's about $600. Having said that ModelRight paid for itself very quickly. I'm a lone-wolf developer/DBA and using this program gave me a force-multiplier equal to hiring two junior DBAs.

    It isn't just about drawing pretty pictures. ModelRight is a CADD tool for database design, not only does it allow you to create the diagram proper, but use that diagram to create the scripts that create the database, alter it, and maintain a detailed data dictionary during initial design. It lets you create user-defined data types (translated to native types in scripts) that automatically set constraints and defaults, set table constraints/indexes with naming conventions you can tailor, set up foreign constraints, and allow you to script it all with a single keystroke.

    I operate on a severely constrained budget, but tools like this cost a pittance when compared to the salaries of two DBAs--and it's a sunk cost, not a recurring one.

    I pick my tools carefully. Between ModelRight and Redgate's SQL Compare/SQL Data Compare it's like having a construction crew with heavy equipment building a canal vs me on my lonesome with a teaspoon...

    The fact I can reverse-engineer third-party databases is just icing on the cake. Since, you know, I needed the tool for development anyway.