Do you use Database Diagrams?

Visual Studio Team Editon for DB Pro - Mairead's version : Do you use Database Diagrams?

Weddings — Fri, 06 Jun 2008 09:02:10 GMT

Like my recent post on database projects, we would like to understand your current usage of the Database Diagrams feature which shipped as part of Visual Studio 2005 and SQL Server 2005. Getting feedback on your usage, helps us to get a deeper understandin

Visual Studio Team Editon for DB Pro - Mairead's version : Do you use Database Diagrams?

Dating — Sun, 01 Jun 2008 11:34:06 GMT

re: Do you use Database Diagrams?

Karsten Breivik — Thu, 17 Apr 2008 15:55:13 GMT

Truly visionary comments by Ericga and Goober.

I completely agree on the comments from Ericga on the layout algorithm.

It would be nice to have the possibility to choose between orthogonal, hierarchical, tree, and symmertric layouts. There are ready made libraries for managing this, so it should be possible to do.

The real benefit of doing this, is to check for compliance to normal forms. A DB in 3NF should always be possible to lay out graphically 2-dimensionally without having lines crossing. Crossing lines would be a very simple indication of tranistice dependencies indicating that the database is not in 3NF.

Another nice feature would be the ability to somehow group the tables based on their use in stored procedures and views. Could be difficult to implement, but it sure would be fun to have... Say that you list our procedures on the left side of the window and use checkboxes to select procedures which should have their tables grouped in the layout...

Also I agree wih Ericga that "3 dimensional feature" and "fit layout to print" that Ericga speaks of would be a dream come true.

Brgds Karsten

re: Do you use Database Diagrams?

scott ocamb — Sun, 09 Mar 2008 22:22:28 GMT

I typically use diagrams to model the database and communicate it to the users and or developers. I use ENTERPRISE ARCHITECT to model process flows, state diagrams and also data models.

I find this much better than visio.

EA will also generate the sql for the database and allows for very good comments that help users understand.

So, i would like to se some sort of diagram tool in the database version of Visual Studio Team Edition.

re: Do you use Database Diagrams?

Adam Harris — Mon, 21 Jan 2008 18:54:15 GMT

It is the $6m question.

- I suggest strongly to have crow foot instead of the bad key and infinity sign, and to make the relationship single line instead of 3D pipe, with the option to have dotted line at the one, many or both ends, and the option to control the boldness of the line.

- I like to be able to reduce the table width to the minimum.

- I like to add more options on the relationship context menu, such as rename and properties.

- The current relationships can be only at right angle, so I like more control like Visio to make different angles and curves.

- After I edit the database at another environment I like to click refresh on the ERD.

- I like an option to have all tables auto-size in height so to fit all fields.

- we can get more space by reducing the height of each field and the left margin on the text, and reduce the width of the gray square on the left.

- Many more.

re: Do you use Database Diagrams?

Doug Ramirez — Mon, 26 Nov 2007 05:30:44 GMT

I don't use database diagrams because the tools for using them *ALWAYS* exist out of the context that they are needed in. That being the context that models the database that will store the state of your objects in your class model.

As LINQ evolves and bridges the gap from code to database persistence languages, it should make the emphasis on modeling your app first, database later more important.

So using database diagrams really should be renamed application data persistence diagrams that show the 'things in the application' and how they persist data in the 'things in the database.'

An E/R diagram tells you nothing about how the database is used. Yes, tables have columns that are typed, and they have keys and indexes, etc. That picture is void of use and behaviors and rules.

Once the modeling can encompass the classes that use the db schema objects and a diagram of those entities and their interaction are described visually, suddenly that diagram has value and can facilitate substantive conversations amongst the development team.

This is what we need.

re: Do you use Database Diagrams?

Goober — Sat, 05 May 2007 05:39:24 GMT

One more thing:

Show the dang margins when showing page breaks. And don't put anything there when arranging tables.

As it is now, I have to guess if I've encroached on a margin, and if I have, I get 2 each 12 foot long pages! One of them has 10 pixels of a table on it. grrrrr. (Probably my printer driver trying to do the right thing.)

O.K. One more, more thing, then I'm going home. Otherwise I'll be here complaining all nite:

Give me the option not to print page numbers. PRETTY PLEASE.

My plotter is smart enough to cut the paper at the end of the inked area. The problem is that plotters see the page as 36" wide (the roll width) and 12 feet long (my custom paper size, which then has to be set to landscape to get banner). If I create a diagram where I don't need more than 3 feet horizontally I print in portrait mode, where I may only need 1 or 2 feet vertically. The problem is the diagram tool then adds a page number 12 feet down, and of course the plotter sees this and wastes 10 feet or so of paper so it can write a useless page number.

OUCH.

--Goob

re: Do you use Database Diagrams?

Goober — Sat, 05 May 2007 05:20:07 GMT

I'm mainly using diagrams to print out entities and relationships for quick reference. We have several enterprise databases with 1,000+ tables. A 3 foot x 12 foot banner with the entities logically grouped is a priceless time saver we use daily.

So, two thumbs up to ericga who said:

- If there was a way to optimise a diagram based on the page size used for printing, it would save loads of time.

I just spent 4 days making a diagram manually...

I need a layout dialog that lets me pick a page, a logical group of tables/views, and fits them on the chosen page(s).

This would require being able to create logical groups in the first place.

It would be very nice if when 2 relationship lines crossed one of the lines had a little hump over the other line. This would make it possible to actually follow a line.

View options:

1. Line width for relationships.

2. Line color for cardinality.

3. PK icon. Hide it, show PK's and FK's columns as text in brackets following the column name. This will save a ton of horizontal space because all the column names won't have to be shifted the 18 or so pixels they are now.

4. Autosize: truncate options for entity names, column names, etc.

5. Allow relationship lines to run under tables? choices are NO, NO, and NO.

Ditch the vertical scroll bars on the entities. They suck. The 2005 solution of dealing with the sucky issus where they've decided to just autosize to a row and a half of extra vertical white space was not a good idea. They also take too much horizontal room and cause everything to get displaced and shifted when you change zoom level and they magically appear. What good are they when printed? Yikes!!

Perhaps a 1/2 line "more" button, (like some office menus), at the end of the table/view would suffice? Printed you'd see some columns were hidden and when interacting you could hover the mouse to temp expand it, or click to permanent expand.

There's more, but this post is long enough. Simply take a large database and try to print the entities on a roll-fed plotter or ink-jet. You get the idea.

--Goob

P.S. I need my diagrams to be save-able in TFS source control, and NOT hooked to any specific database instance except when I tell it to do so. And not to automatically update itself when it is connected. Give me a dialog that lets me choose what changed entities to update on my diagram and when to make the update.

P.P.S. Thanks for asking. I needed the vent :-))

re: Do you use Database Diagrams?

Ian_E — Sun, 22 Apr 2007 02:34:33 GMT

I use the diagrams mainly for setting up primary key / foreign key relationships between tables.

I really like doing it this way - it's very easy when you have such a visual tool.

I also discovered that, when setting up your relationship, you can specify an optional "Delete Rule" for what happens in a child table when the corresponding entry in a parent table is deleted. I implemented cascading deletes in this way without having to create a database trigger. I am confused however about why the "Delete Rule" is found in the "INSERT And UPDATE Specifications" category.

My only issue with the diagram tool is that you cannot go back & edit a relationship after you've created it (at least I haven't figured out how); consequently I just delete the existing relationship and then create a new one.

re: Do you use Database Diagrams?

Ericga — Sun, 15 Apr 2007 17:47:41 GMT

I am using SSMS because VS is slow as a turtle on valium. I use VS for C# development only. (should also point out that SSMS is always installed even on a production server but it is sometimes totally impossible to install VS or connect to a db with VS because of security policies).

Other reason to use it: the visual is stored WITH the database. It is also orders of magnitude faster that Visio. I prefer to do without than use Visio.

- some windows can't be resized and some are way too small. Check constraints comes to mind (that one is really stupid). A composite window for editing would be better than all those small dialogs.

- would love it if it stopped adding parenthesis each time a check constraint is saved. When comparing schemas we end up with differences like ((((<check)))) versus (<check>) simply because one has been modified a few times with the designer.

- current support for schemas and collations is awful

- designing FK is a royal pain when you link to unique constraints instead of PK.

- supporting views of course. Also supporting "implicit" FK with views. Obviously SQL2k5 does not support FK to views or between tables of different dayabases, but explaing relationships between views and tables is still important. Those relations are a different beast (part of the diagram but not the schema) and should display differently (dotted for example). Also useful to show relations to external entities (other db, other server, etc.)

- the "re-arrange" algorithm is mostly a mess. Lose way to much time trying to manually dispose tables correctly. Improving (well, replacing) the algorithm (hierarchical layout would be better) and/or supporting more than one. Being able to pin tables in the layout so we don't lose manual work.

- support for colors would be good. Color laser printers are now common.

- being able to export the visual to PDF, PNG and WMF would be good. XAML should be seriously considered.

- much better support for user-defined extended properties would also be good (LARGE text box please. It stores up to 7k).

- being able to save a change script instead (or on top) of executing it is necessary.

- it is painfully slow to work with large (1000+ tables) databases. Using profiler it is obvious that way too many queries are issued (row by row processing is used many places)

- it does not support multiple monitors correctly. You never know were a window will pop and SSMS can even open on a monitor that is not there anymore.

- If there was a way to optimise a diagram based on the page size used for printing, it would save loads of time.

- being able to easily filter the list of tables using the LIKE syntax would help (RegEx is great but most dbas are not familiar with it and everybody is familiar with the LIKE syntax)

- if there was a way to save the diagram in xaml and have a special reader that could display, zoom, navigate dependencies, search, etc. it would be a killer. Being able to distribute the schema to developers so they can consult it easily (see the relations and the extended properties annotations) in a WPF environment would be great. (I can dream, can't I:-). Not sure if navigating your schema in 3D would be useful, but it sure would be fun lol. That would be a very popular WPF showcase among developers to say the least.