Complex database diagram -- linking across sheets?

Started by cmullican, April 08, 2016, 06:28:27 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

cmullican

We have a complex database diagram in Visio 2010 (since the DB diagramming support is lacking in the newer versions), which I'm trying to update.  There's an overview page and many subsystem pages.  I would like, if possible, to link entities across pages so that adding a column on a subsystem page updates the entity on the overview page.  If I can link even more -- a container on a subsystem page is mirrored on the overview page, so that adding an entity to the container adds it on the main page, even better.

I've looked at the ShapeSheets for some of the entities, since excessively complex formulae seem to be the solution I'm seeing for linking, but I don't see a way to apply those solutions to this use case.

Any tips, even if it's just useful keywords to Google, as my efforts so far have lead nowhere?

Nikolay

#1
Can't you just go for Visio 2010 and that's it for now?
Microsoft may be bringing the "real" database diagram back, as far as I know (with some not-that-distant update to come)

cmullican

Yes, the question is how I can link the entities across pages in the Visio 2010 document so I don't have to update each table in 2 or more places.

Nikolay

#3
Sorry, it seems I'm missing the point then.. It seems I've not read the first message properly.
What I meant was that normally when you add column, it's added to all shapes representing the table.
But apparently that does not seem what you are after.

do I understand you correctly that you are looking for some sort of "simplified views" of the pages to be available on the front page?
Like, set of sub-system tables (without details). So that it is synchronized with corresponding page when you add/remove tables.
Or?

cmullican

#4
Yes, but I didn't create this document, so something may not have been set up correctly to allow that. I'm just trying to update it to reflect the current state of the database.

On the "Main" tab, which is the overview, there's an entity for "defAreaCode".  On the Geopolitical tab, the same entity exists.  However, adding a column to one does not add it to the other.  Now, multiply this by a few hundred tables, some of which are in multiple subsystems, and that's what I'm trying to update.

cmullican

I want to know how to make a shape representing the table appear on more than one page, so that when I alter one, it applies to the others.

Nikolay

Aha, so it's a column then, not summary.
On the "database" tab, there is checkbox "Tables and Views". Check it, this will show the "tables and views" (model explorer) window.
You can drag-drop the table you want from there to the pages where it is supposed to be. Then both shapes will be bound to the same entity (and update simultaneously, respectively)

cmullican

#7
OK, that's a start!  Since this is already in a non-ideal state, with multiple copies or certain tables, (and was when I got it), a few more questions:
Is there an easy way to tell which of the 'defAreaCode' entries in the list is the one on "Main" and which is the one on "Geopolitical"? (etc. -- several hundred tables, many tabs)
(nevermind, figured this one out)

Is there a way to replace the one on "Main" with the one from "Geopolitical" without having to re-create all the relationship connectors? Or any other way  to clean this up, short of recreating the main diagram table-by-table?

Nikolay

Unfortunately I don't know an easy way to accomplish this (replace "wrong" tables with "real" ones)...
Visio saves the model, so if model is incorrect, it needs to be fixed (and this assumes fixing the connections..)

cmullican

Well, that's probably another week of work for me, then.

One more may-not-be-possible:

Some tables should be displayed on multiple subsystem tabs. When I add a copy of  the canonical table to the page and try to move or recreate the relationships, if keeps deleting the FK from the child table -- even if I link it to the new parent table first. 

If I just drag the arrow to the new table, it deletes the column immediately, before I can update the relationship.

If I link first and answer "no" when asked about deleting from the model, the entry in the child table ends up marked as part of two FK relationships, even though there's only one actually.

If I link first and answer "yes", it deletes the column, even though the new relationship still references it.

I _really_ don't want to have to recreate all these columns if I don't have to...is there a workflow I'm missing?

Nikolay

I would recommend you to try fixing connections not by drag-n-drop, but with model explorer.
To avoid column removal, first drop the connection with it, i.e. click "disconnect"

cmullican

That makes enough sense that I now feel it should have occurred to me!  I'll give it a try when I get back to work Monday. 

cmullican

It's going much more smoothly now, but I've noticed one thing: if two tables have an FK relationship defined before I add them to another page, the connector for that relationship appears when I add the second table.

But if I add a FK relationship between two tables on one page that are already both on a second page, it does not appear between the two on the other page. The FK# does appear before the column name, but no connector appears.