Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Modeling and Diagrams with SQL Server 2008


Database Modeling and Diagrams with SQL Server 2008

Author
Message
randy-719186
randy-719186
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 41
The designer is nice, but limited. I recommend modeling every aspect of your project using a dedicated design tool (I use PowerDesigner) once you have generated the DB the Designer is really good at tuning things.
R M Buda
R M Buda
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 295
I like the article - it gives a clear rundown on the pros and cons of the simple built in diagramer, which I have found very handy.

The fact that the diagrams are tied to the actual system can be an advantage.
a) It encourages the use of referential integrity
b) There can be no "stale" diagrams

I always try to use referential integrity and set NOCHECK if the actual checking gets in the way.
The ref integrity constraints make for an instant diagram and also the relationships can be checked at some point by DBCC CHECKCONSTRAINTS. This is great for ETL processing where you may blow away the records in a lookup table and replace them later. At the end of all processing run DBCC CHECKCONSTRAINTS to see if any constraints are violated.
peter-757102
peter-757102
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 2549
Where I work DD is used a lot by the other developers, so everyone but me as I very much dislike it.

The problems I have with it are:

1. It does modifications directly to the DB and in doing so recreates whole tables and relationships. This argument is not exclusively related to DD, but to any table modifications you do via the interface. In my view none of it is designed to be used extensivly on a filled database.

The problem is compounded when you need to move changes from the development to a test or production environment. Suddenly you need an database comparison tool as there have been no modification scripts made along the way. This also makes it hard to do partial updates.

Also, when I design a new (sub)-model, I want to be able to drop everything and recreate from scratch in the blink of an eye. This is very easy when you work from scripts or a tool that supports you in maintaining and updating those, but very hard with any direct action UI.


2. DD hides details that are very important for the quality of the modeling. I want to see at a glance what indexes there are, what fields are covered by them and any other relevant parameter that influences performance.

To have this kind of information in a consumable fashion I really need a create script that is compact and shows you these details in a declarative and well formatted form. This also helps me to keep an up to date modification script that can be directly applied to the target environment.


3. Organizing and printing is a pain for anything but the smallest of (sub-) models. I have used full blown modeling packages in the past such as Power Designer (Data architect module) from Sybase and its predecessor S-Designer. They are very expensive, but did work very well for me as you can model and do versioning, independent of the database and can generate update scripts between modeled versions automatically. It also features well functioning reverse engineer functionality which comes into its own when you are confronted with a database for the first time.

Just my 2c
Roger L Reid
Roger L Reid
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 166
One reason why they are poorly used (aside from the primary one - no one designs databases anymore, folks just throw a bunch of tables together) is that, for anyone working at the level that requires real modeling work, MS's feature is too restrictive.

I don't want to use one tool for SQL Server, another for ASE, and another for Oracle; to my firm, it's all 'the database'.

It may be better now, but also MS tends to limit choices of representation in tools like this, and then drop support as they run to the next marketing term. The best example is the (former) support for database modeling in Visio: MS would let you download modules to do this in the previous release, but they don't work in the newest Visio, and they have no plans to make it work.

That's why, to get serious about modelling and documentation in a non-trivial environment, you're out there looking at third party products.

Roger L Reid
Rajib Bahar
Rajib Bahar
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 245
The article covers the diagram feature well. I got an email with the subject line "Database Modeling and Diagrams with SQL Server 2008". It led me to think this article will talk about new features in SQL Server 2008. Most of the features covered in the article were already there in earlier versions. This article does not clearly indicate which features are propriety to SQL 2008 only.

I missed Steve's comment...

"Not everyone has been using SQL Server for more than one version. We get new people all the time and publish articles to help all levels of users."

In light of that, I think it was fair for the author to title it as he thought was proper.

Rajib Bahar
http://www.rajib-bahar.com
http://www.twitter.com/rajib2k5
http://www.youtube.com/icsql
Roger L Reid
Roger L Reid
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 166
I hear you on the advantage of linking for the physical model - otherwise its out of date before its done. However, that means its not really a modeling tool. That's why the third party products are needed; so that you can work with the logical and conceptual models and not be tied to the physical too early.

In the end, it has to be implemented as a physical model, of course, and that's where it's good to get back the "truth".

By the way, the article itself is very nice and useful. I'd had some trouble navigating around it before, so for using the tool itself, the article is a big help.

Roger L Reid
mojo-168709
mojo-168709
SSC Eights!
SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)

Group: General Forum Members
Points: 923 Visits: 831
It's a good article only if it states at the very beginning:

Do NOT try this in the production environment !!!!!

Once you make a mistake, it will be reflected in the production database. Get your resume ready:-P
sbright-613196
sbright-613196
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 250
Anybody knows how to use smaller font in DB Diagram so that I can fit more tables in 1 printed page?
AntonDeski
AntonDeski
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 33
Printing Tip for DDs:

Use Page setup to change the page size to A3. Show the page breaks and clean up the diagram. Then shrink print to A4. Still readable and much more information per page.
sbright-613196
sbright-613196
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 250
Thanks!!! It helps.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search