April 5, 2011 at 2:40 pm
Hoping to get a good recommendation on a database design/diagramming/modelling tool.
Right now I'm using Visio 2011 and reverse engineering projects and using it to diagram designs for new tables.
But we have a new project coming that basically involves normalizing some data, and removing some other columns in a fairly complex database (500+ tables).
I'm looking for a tool that I can use that does the following.
1. Let's me redesign the database (remove columns, create foreign keys, etc) in a simple drag and drop/clicky methodology.
2. Will let me preview what all the changes will be (i.e. if a column is dropped, would show it in the table with a line drawn through it, or in a different color or something, if a column is added, maybe color it green or something).
3. Will generate the change scripts so I can look at them and tweak them if necessary
4. Supports the latest edition of RTM SQL (2008 R2)
Anybody have a tool like this?
April 6, 2011 at 5:43 am
My favorite software for doing this is Embarcadero ERStudio. It meets all your requirements and quite a few more. If you're going to work on your databases through a modeling tool, you could do worse.
I wouldn't skip getting your database into source code along side your app though, so think about how you want to integrate that too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2011 at 7:16 am
Grant Fritchey (4/6/2011)
My favorite software for doing this is Embarcadero ERStudio. It meets all your requirements and quite a few more. If you're going to work on your databases through a modeling tool, you could do worse.I wouldn't skip getting your database into source code along side your app though, so think about how you want to integrate that too.
Well the plan is to work on the design through the modelling tool. Getting the change scripts is more bonus. The main purpose is to be able to build a diagram that shows the impact of a requested change so that we can then identify the various applications that may be impacted by the change.
I know in the perfect world it would be all SP changes, and for our new stuff it is. But our older inherited applications are VB6 apps that throw queries at the system. So there's always code to dig into.
Just out of curiosity, what purpose does getting my database into source code (I'd assume I'd do that by script exporting a database via SSMS) give me? If the database I was working on was one that the company sold as opposed to just used in house to manage the business, I could see the value of it, but when I have backups and regularly test said backups of the production system, I don't see what value having scripted copies of all the create scripts would bring me.
I'm probably overlooking something, but I figured I might as well ask. 🙂
April 6, 2011 at 8:25 am
mtassin (4/6/2011)
Grant Fritchey (4/6/2011)
My favorite software for doing this is Embarcadero ERStudio. It meets all your requirements and quite a few more. If you're going to work on your databases through a modeling tool, you could do worse.I wouldn't skip getting your database into source code along side your app though, so think about how you want to integrate that too.
Well the plan is to work on the design through the modelling tool. Getting the change scripts is more bonus. The main purpose is to be able to build a diagram that shows the impact of a requested change so that we can then identify the various applications that may be impacted by the change.
I know in the perfect world it would be all SP changes, and for our new stuff it is. But our older inherited applications are VB6 apps that throw queries at the system. So there's always code to dig into.
Just out of curiosity, what purpose does getting my database into source code (I'd assume I'd do that by script exporting a database via SSMS) give me? If the database I was working on was one that the company sold as opposed to just used in house to manage the business, I could see the value of it, but when I have backups and regularly test said backups of the production system, I don't see what value having scripted copies of all the create scripts would bring me.
I'm probably overlooking something, but I figured I might as well ask. 🙂
If the database is one you develop along with an application, then you get what the app gets, labeled versions, branching, etc., all from a deployable location that can be coordinated directly with the app code so that you can know that the database & app are the same.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2011 at 6:58 am
Grant Fritchey (4/6/2011)
My favorite software for doing this is Embarcadero ERStudio. It meets all your requirements and quite a few more.
+1 on ERStudio. Definitely the tool of choice for database (and application for that matter) modeling.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 7, 2011 at 9:58 am
I've downloaded the free trial of "Datanamic DeZign for Databases". It has a powerful "reporting" option for creating a report that include the diagram, and the whole entities allowing to add description for each class or entity and can be exported to different formats (Word, HTML, or PDF).
About your request of tracking changes of your database, I'm not sure about it. However, you can save tables definitions and stored procedures for each database amendment.
http://www.datanamic.com/dezign/index.html
Regards,
Hafiz Ghazal
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply