Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Database Modeling and Diagrams with SQL Server 2008 Expand / Collapse
Author
Message
Posted Tuesday, May 19, 2009 7:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 1, 2011 11:29 AM
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.
Post #719818
Posted Tuesday, May 19, 2009 7:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 16, 2014 2:20 AM
Points: 146, Visits: 245
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.
Post #719834
Posted Tuesday, May 19, 2009 8:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 6:21 AM
Points: 334, Visits: 2,273
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
Post #719844
Posted Tuesday, May 19, 2009 8:17 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:32 AM
Points: 108, 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
Post #719868
Posted Tuesday, May 19, 2009 8:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 8:16 AM
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
Post #719918
Posted Tuesday, May 19, 2009 10:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:32 AM
Points: 108, 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
Post #720046
Posted Tuesday, May 19, 2009 12:12 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 10:46 AM
Points: 911, 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
Post #720082
Posted Tuesday, May 19, 2009 1:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:12 AM
Points: 3, Visits: 231
Anybody knows how to use smaller font in DB Diagram so that I can fit more tables in 1 printed page?
Post #720106
Posted Tuesday, May 19, 2009 3:35 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 14, 2013 12:34 AM
Points: 214, 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.
Post #720181
Posted Tuesday, May 19, 2009 4:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:12 AM
Points: 3, Visits: 231
Thanks!!! It helps.
Post #720210
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse