So, I have recently been designing a schema for a new database system. As everyone knows, this is a technical process and requires a good understanding of the problem domain to make sure you collect all the business concepts and relationships.
For these kind of things, you can't beat a good entity relationship diagram to visually see the makeup of your database tables and the relationships between them.
This post is about the "Toad Data Modeller" application which is what I've been using to model my database design. It's such a great tool that I think it should be present in the toolbox of any DBA.
Now, I know Microsoft includes a rudimentary diagramming tool in SQL server. Just right-click on "Database Diagrams" and add the tables you want to include.
But this feature hasn't been shown any love since SQL Server 2000 and the diagrams are very simple. Microsoft Visio is another tool I've previously used and it allows you to create an ERD of your database, although I think you may need the professional version to reverse engineer an existing database. It's not intelligent in any way apart from creating a pretty diagram.
But my new favourite tool to use is Toad's Data Modeler.
Below is an example screenshot of Toads Data Modeler on the sample "AdventrueWorks" database.
It allows you to reverse engineer (read in) an existing database, although the freeware version (the one I'm using) only allows you to reverse engineer a maximum of 25 tables at a time.
Once the model has been loaded you can easily add foreign keys between tables, add unique constraints, and basically ensure there are no floating tables in your design. You can hover over constraints to see which database fields they are attached to. If you have made changes to the actual database schema under the covers, you can "Update the model from the database". And if you have made any changes in the app, you can export the SQL.
In short, it's awesome! It's a great tool to use to scan over your tables to make sure all the required relationships of each individual table is met. Oh, and did I mention it works with all the major database systems. And its free!
If you haven't got it download it from here.
