LinkedIn | FaceBook | Twitter
Yesterday at TechEd 2010 here in New Orleans I worked the front-booth, answering general SQL Server questions for the masses. I was actually a little surprised to find most of the questions I got were from folks that wanted to know more about Stream Insight and Master Data Services. In past conferences I've been asked a lot of "free consulting" questions, about problems folks have had from older products. I don't mind that a bit - in fact, I'm always happy to help in any way I can. But this time people are really interested in the new features in the product, and I like that they are thinking ahead, not just having to solve problems in production.
My presentation was on "Database Design in an Hour". We had the usual fun, and SideShow Bob made an appearance - I kid you not. The guy in the back of the room looked just like Sideshow Bob, so I quickly held a "bes thair" contest, and he won.
Duing the presentation, I explain the tools you can use to design databases. I also explain that the "Database Designer" tool in SQL Server Management Studio (SSMS) isn't truly a desinger - it uses non-standard notation, doesn't have a meta-data dictionary, and worst of all, it works at the physical level. In other words, whatever you do in SSMS will automatically change the field/table/relationship structures in the database. We fixed this in SSMS 2008 and higher by adding an option to block that, but the tool is not a good design function nonetheless. To be fair, no one I know of at Microsoft recommends that it is - but I was shocked to hear so many developers in the room defending it as a good tool. I think the main issue for someone who doesn't have to work with Relational Systems a great deal is that it can be difficult to figure out Foreign Keys. The syntax makes them look "backwards", so it's just easier to grab a field and place it on the table you want to point to.
There are options. You can download a couple of free tools (CA has a community edition of ER-WIN, Quest has one, and Embarcadero also has one) and if you design more than one or two databases a year, it may be worth buying a true design tool. For years I used Visio, but we changed it so that it doesn't forward-engineer (create the DDL) any more, so it isn't a true design tool either.
So investigate those free and not-so-free tools. You'll find they help you in your job - but stay away from the Database Designer in SSMS. Or I'll send Sideshow Bob over there to straighten you out.
I love your blogs. Short & informative.
Don't underestimate the Database Designer.
- Sure, it isn't a logical design tool. But there are plenty of those.
- We know, it can trash a production system & blow away the data from a Dev system, if you let it. But it has an option to stop it & the Visual Studio Data dude is a tool for that scenario.
But it is great for :-
+ Quickly understanding an existing database schema, you've never used before.
+ Adding Comments to Columns, Tables etc to improve the documentation of the schema.
+ "Thinking out loud" design. You create the tables via it, or script & add the objects into your diagram. You can quickly add sample rows into your table, via script. Then run queries to show end-users what they will get. This lets you have a design discussion that they can understand. You have near zero commitment to the design, so can quickly trash & change to suit.
It is surprising how these type of discussions with Business users result in the "Oh I forgot to tell you about this entire area of our business, we need 3 new tables ..."
Once finished you can easily script it & add rigour.