Why do we use data modeling tool for database design for MS SQL or MySQL or MS Access databases?
While working with different software developers, I noticed that some of them still using spreadsheets or MS Word to keep track of the database design. Other developers use the MS SQL Database Diagrams in MS SQL.
Visual Studio provides some basic capabilities on the database design. But is that the most productive tool to use?
When interviewing some developers for my company with ten years of experience or more, I found that some did not know the difference between a clustered index and a regular index. A clustered index is an entire subject on its own. Why do custom software developers neglect their database design skills? I’m not so sure I understand.
Some of the software developers told me that they did not deal with a complicated database, and the need was not there to use a tool to design any database. Well, I am not convinced that this is a valid excuse.
Sometimes I see developers that don’t even optimize their stored procedures and don’t even bother to use an execution plan to see how the performance of the stored procedure can be improved. Here is a free tool that can do just that: SQL execution plan viewing and analysis and made by ApexSQL.
I would use a data modeling tool to design any database whatsoever, whether it is a simple one or complicated. The productivity gained is tremendous. Not only that, you get a global view of the entire system. It will create the script for every change you make within the database, and that script can be executed within the data modeling tool. All changes are done within one tool and no need to go back and forth from tool to another. Why not use such a tool to automate the process?
It handles Stored Procedures, triggers, views, indexes, tables, and relationships. The nice thing about the relationships feature is that you can add text in any relationship between each entity and in a different color.
This is an actual database design we did while using the xCase data modeling tool.
If you make mistakes it lets you know. It is a graphical interface and easily you can track down all relations from one entity to another and simply by following the color. So why wouldn’t you use this type of tool to design your database? This is mind boggling for me. For our company our favorite tool is xCase, Toad could be another nice option to use. See this free data modeling tool that can get you started: Design SQL Server Data model diagrams integrated with SSMS, Visual Studio and source control and it was created by ApexSQL.
Another inexpensive tool is from the dbSchema company and could be found here: Schema Diagram Designer and Query Tool.
You can easily reverse engineer the database and grab all the details of the entire database in record time. Check this article to see a video we did on that subject: Reverse Database Engineering and Database Design. You may check this article we did as reference on this topic here: Can you optimize my ms sql or mysql database?
In general, we provide Remote Database Administrator services and also known as Remote DBA and efficiency the way we work is a must. Design the database the right way is extremely important before you write any code in the back end or the front end. This way your stored procedure can be easily optimized with the correct database design. Part of it is due to the features set that allow you to take advantage of a data modeling tool's design capability.
If you have any questions, you may contact us at: 732-536-4765
Check here for more details about the Custom Software Development solutions we provide using the tools mentioned above.