At work our primary web application is supported or driven by a database that is not relational, has lots of redundant data, some tables don’t have Keys, there are not Foreign Keys or indexes. For those DBA’s that found this article, it would be a nightmare. So, with the database concerns and other architecture and security concerns, I pushed to do a re-design of the database and re-write of the web application.

Besides this database, our other databases are small and/or not complicated. So, when I went looking for a data modeling tool to assist in visually designing and laying out related tables, I didn’t want to purchase an expensive tool, maybe some free tool or online service would do. I reviewed a few and the one I chose to try was SQL Database Modeler.  It was simple and easy to use. I am not aware of any table (or entity) limitations, but after 21 tables I wanted to test the creation script and make sure everything would work before I got farther along. Well, the generated script would not completely execute due to foreign keys, certain tables need to be generated first. SqlDBM was in beta at the time, it may have had settings or configurations available to adjust this problem… but I set it aside and tried a few other tools.

I then stumbled upon Toad Data Modeler (TDM). I had used Toad as a SQL coding tool back in the day with Volvo Commercial Finance because they were using Oracle database. Quest offers TDM free for 30 days for evaluation. I thought, great I will be done in that time. Well, TDM is simple and powerful. Anything you wanted to set up regarding your tables, keys, foreign keys, indexes, everything. To not lose what I build with SqlDBM, I removed all the foreign keys so the script could run and generate my tables. I then imported that schema into TDM and continued building out the database… until one time I went to save my model with a message that I exceeded the table limit of 25 and would need to purchase a license.

Shame on Quest for not mentioning the limitations right on the web page where the free trial of the software is mentioned and where you register for it.  Not even a mention of limitations or link for trial limitations. Oh well…

I brought this up to management and we quickly purchased the software, reasonably priced. TDM auto generates an index on foreign keys for each table. When I imported the first group of tables, TDM auto determined primary keys and their matching relationships in other tables. The auto-generated relationships and indexes do not make sense “Relationship #1”, “Relationship #2” and the same with indexes… but they have macros to assist renaming these to your naming conventions. They did not have a macro for renaming the indexes, but with my help in the Toad Community Forum, they do now.  Now at 53 tables, I am quite pleased with the model and TDM.