How to connect tables when they are in different pages of the data model document?

[Ricardo]

I am pretty new to visio and I am using it to create a database data
model for our application. The situation I have is that my data model,
so far, is two pages long. I have tables on the secod page that have
foreing keys to tables on the the first page. Is there a way to
establish this relationship when the tables are in different pages of
the document? I know how to establish this relationship when the
tables are in the same page but not in different pages. Thanks.

 

[John Saunders]

You can create a new page, and drag both tables to it.

 

[Ricardo]

You can create a new page, and drag both tables to it.

FYI: I am using Visio 2000.

I already had a new page. On this new page I have tables that have
foreign keys that point back to a table defined on the first page. I
need to keep this table on the first page since there are other tables
on that first page that also have foreign keys to this same table.

I tried to drag the parent table from the first page to the second
page hoping that visio would "make a copy" (not move) of it on my
second page. However, that didn't work. It just didn't allow me to do
it.

Any more suggestions/ideas?

 

[John Saunders]

Sorry, I was thinking Visio for Enterprise Architects 2002, in which case
the table is an entity distinct from its appearance on a drawing. All I'd
have to do is create a third page and drag the tables I wanted from the
Tables list onto the third page, then create the relations.

 

[Karsten Farrell]

Hi Ricardo, thanks for writing this:

FYI: I am using Visio 2000.

I already had a new page. On this new page I have tables that have
foreign keys that point back to a table defined on the first page. I
need to keep this table on the first page since there are other tables
on that first page that also have foreign keys to this same table.

I tried to drag the parent table from the first page to the second
page hoping that visio would "make a copy" (not move) of it on my
second page. However, that didn't work. It just didn't allow me to do
it.

Any more suggestions/ideas?

Since you don't have the Enterprise Architect version, you must be using
Visio only to draw a diagram of your database (which is how I use it, by
the way, and worth taking the time to draw, even if I can't generate the
db from it). You have at least a couple of choices:

1. You can create a "dummy" entity of the parent or child table off the
page, in the non-printing area. You only need put the PK and/or FK in this
"dummy" table. You can then draw the relationship between the visible
table and the invisible one. The problem is that your relationship lines
will print with the line going off the edge of the page into never-never
land.

2. You can create your "dummy" table on a non-displayed, non-printing
fantom layer and connect your relationship lines as above. Then on the
displayed layer, put an off-page connector that's lined up - by eye - with
the line that connects to the fantom.

3. What I do is list all of the off-page tables in a shape from the
Callouts template called "Full bracket text." That's just my convention
for my drawings ... you can use shape you wish. The callout lists the page
number and table name of the parent or child. Too bad I can't make a nice
graphic picture to post here, but it ends up looking something like:

Parent
+-------+ +-----
| PK | | Pg Table
+-------+--->| --- -------
| ColA | | 4 ChildX
| ColB | | 2 ChildY
+-------+ +-----

If your newsreader doesn't display the above in a fixed-width font like
Courier, then you'll have a real messy looking diagram.

PS - Since I don't need to worry about generating a db from my Visio
diagram (it's just a pretty picture), I take other shortcuts.

All my datatypes are the default (so I don't have to worry whether it's a
number or a string value). I do check/uncheck the PK checkbox so PKs are
shown above the columns. I don't worry about the required checkbox (it
doesn't show on the diagram). I don't use "relationship" shapes (I draw
simple dynamic connectors) so I don't have to worry about matching PK/FK
columns.

But these shortcuts are mostly because I'm happy just to have a pretty
drawing ... and that challenge is enuf for me. I'm not saying it's the
best approach. YMMV.

 

[Chang Oh [MSFT]]

You can drag any existing table from Tables window (Database > View >Tables) on to the diagram to make a second (or third, ..) graphical instance of the same
table. So, if you want to connect table A on page one with table B on page 2, you can either drag B onto page 1 to connect or A onto page 2 to connect.

Also note that creating a new graphical table instance will result in displaying all relationships that are already defined with other tables on the page. To simply hide
some of the relationships, you can just remove the line without deleting it from the underlying model.

--

Chang Oh
Visual Studio Enterprise Frameworks and Tools

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
--------------------
From: Karsten Farrell <[email protected]>
Newsgroups: microsoft.public.visio.database.modeling
Subject: Re: How to connect tables when they are in different pages of the data model document?
Message-ID: <[email protected]>
References: <[email protected]> <[email protected]> <abeafe5d.0307300808.45e57d15
@posting.google.com>
X-Newsreader: MicroPlanet Gravity v2.60
Lines: 82
NNTP-Posting-Host: 67.117.210.172
X-Complaints-To: (e-mail address removed)
X-Trace: newssvr13.news.prodigy.com 1059595323 ST000 67.117.210.172 (Wed, 30 Jul 2003 16:02:03 EDT)
NNTP-Posting-Date: Wed, 30 Jul 2003 16:02:03 EDT
Organization: SBC http://yahoo.sbc.com
X-UserInfo1: [[PA@SVDSZYMR]PXKROD]_\@VR]^@B@MCPWZKB]MPXHJUZ]CDVW[AKK[J\]^HVKHG^EWZHBLO^[\NH_AZFWGN^\DHNVMX_DHHX
[FSQKBOTS@@BP^]C@RHS_AGDDC[AJM_T[GZNRNZAY]GNCPBDYKOLK^_CZFWPGHZIXW@C[AFKBBQS@E@DAZ]VDFUNTQQ]FN
Date: Wed, 30 Jul 2003 20:02:03 GMT
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newsfeed.freenet.de!194.168.4.91.MISMATCH!newspeer1-
gui.server.ntli.net!ntli.net!in.100proofnews.com!in.100proofnews.com!prodigy.com!newsmst01.news.prodigy.com!prodigy.com!
postmaster.news.prodigy.com!newssvr13.news.prodigy.com.POSTED!34d3422d!not-for-mail
Xref: cpmsftngxa06.phx.gbl microsoft.public.visio.database.modeling:2973
X-Tomcat-NG: microsoft.public.visio.database.modeling

Hi Ricardo, thanks for writing this:

FYI: I am using Visio 2000.

I already had a new page. On this new page I have tables that have
foreign keys that point back to a table defined on the first page. I
need to keep this table on the first page since there are other tables
on that first page that also have foreign keys to this same table.

I tried to drag the parent table from the first page to the second
page hoping that visio would "make a copy" (not move) of it on my
second page. However, that didn't work. It just didn't allow me to do
it.

Any more suggestions/ideas?

Since you don't have the Enterprise Architect version, you must be using
Visio only to draw a diagram of your database (which is how I use it, by
the way, and worth taking the time to draw, even if I can't generate the
db from it). You have at least a couple of choices:

1. You can create a "dummy" entity of the parent or child table off the
page, in the non-printing area. You only need put the PK and/or FK in this
"dummy" table. You can then draw the relationship between the visible
table and the invisible one. The problem is that your relationship lines
will print with the line going off the edge of the page into never-never
land.

2. You can create your "dummy" table on a non-displayed, non-printing
fantom layer and connect your relationship lines as above. Then on the
displayed layer, put an off-page connector that's lined up - by eye - with
the line that connects to the fantom.

3. What I do is list all of the off-page tables in a shape from the
Callouts template called "Full bracket text." That's just my convention
for my drawings ... you can use shape you wish. The callout lists the page
number and table name of the parent or child. Too bad I can't make a nice
graphic picture to post here, but it ends up looking something like:

Parent
+-------+ +-----
| PK | | Pg Table
+-------+--->| --- -------
| ColA | | 4 ChildX
| ColB | | 2 ChildY
+-------+ +-----

If your newsreader doesn't display the above in a fixed-width font like
Courier, then you'll have a real messy looking diagram.

PS - Since I don't need to worry about generating a db from my Visio
diagram (it's just a pretty picture), I take other shortcuts.

All my datatypes are the default (so I don't have to worry whether it's a
number or a string value). I do check/uncheck the PK checkbox so PKs are
shown above the columns. I don't worry about the required checkbox (it
doesn't show on the diagram). I don't use "relationship" shapes (I draw
simple dynamic connectors) so I don't have to worry about matching PK/FK
columns.

But these shortcuts are mostly because I'm happy just to have a pretty
drawing ... and that challenge is enuf for me. I'm not saying it's the
best approach. YMMV.

 

[Karsten Farrell]

Yes, your technique works if your database isn't too complex (like a demo
database). However, as the complexity increases, this technique begins to
suffer from a few drawbacks - including:

1. What happens if Table B has a dozen or more columns? When you drag it
onto page one, you'll have to make room for this monstrous rectangle. Most
of the time, that's why I split the diagram into pages ... because I have
too many of those big rectangles to fit on one page.

2. The problem would be compounded if Table A has a dozen or so parent or
child relationships. I'd have to drag Table B thru Table n onto the page.
Again, having too many tables on a page is why I split it to begin with.

3. Having Table A appear on multiple pages with different sets of FK lines
to Table B thru Table n makes it hard to determine which is the
"official" Table specification. Is Table A on page one, with 4 child
tables correct ... or is Table A on page ten, with 2 child tables correct?

I finally adopted my technique (outlined below) to answer these drawbacks:

Answer to Drawback 1: instead of having to fit a big rectangle on the
page, I use just one line of text in a shape.

Answer to Drawback 2: multiple parent / child relationships appear in a
single shape (well, actually, two shapes since I have one for parents and
one for children).

Answer to Drawback 3: Table A appears only on one page of my ERD, so I
don't have to mentally merge the table appearing on multiple pages.

My technique may not be the "best" approach, but it does fit my more
complex database. It works for me. Sorry to take so many lines to describe
it.

Hi Chang Oh [MSFT], thanks for writing this:

 

[Chang Oh [MSFT]]

Hi, Karsten,

Thanks very much for the information. Your experience and "tips" on how to manage large schema will surely help a lot of folks.

--

Chang Oh
Visual Studio Enterprise Frameworks and Tools

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified
at http://www.microsoft.com/info/cpyright.htm
--------------------
From: Karsten Farrell <[email protected]>
Newsgroups: microsoft.public.visio.database.modeling
Subject: Re: How to connect tables when they are in different pages of the data model document?
Message-ID: <[email protected]>
References: <[email protected]> <[email protected]>
<[email protected]> <[email protected]>
<[email protected]>
X-Newsreader: MicroPlanet Gravity v2.60
Lines: 133
NNTP-Posting-Host: 67.117.210.172
X-Complaints-To: (e-mail address removed)
X-Trace: newssvr13.news.prodigy.com 1059757578 ST000 67.117.210.172 (Fri, 01 Aug 2003 13:06:18 EDT)
NNTP-Posting-Date: Fri, 01 Aug 2003 13:06:18 EDT
Organization: SBC http://yahoo.sbc.com
X-UserInfo1: [[PAPDONYRWMQQT[@JJ@NFXBWR\HPCTL@XT^OBPLAH[\RSAANVUEAE[YETZPIWWI[FCIZA^NBFXZ_D
[BFNTCNVPDTNTKHWXKB@X^B_OCJLPZ@ET_O[G\XSG@E\G[ZKVLBL^CJINM@I_KVIOR\T_M_AW_M[_BWU_HFA_]
@A_A^SGFAUDE_DFTMQPFWVW[QPJN
Date: Fri, 01 Aug 2003 17:06:18 GMT
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newsfeed.icl.net!newsfeed.fjserv.net!
newsfeed.news2me.com!elnk-nf2-pas!elnk-atl-nf1!newsfeed.earthlink.net!prodigy.com!newsmst01.news.prodigy.com!prodigy.com!
postmaster.news.prodigy.com!newssvr13.news.prodigy.com.POSTED!34d3422d!not-for-mail
Xref: cpmsftngxa06.phx.gbl microsoft.public.visio.database.modeling:2983
X-Tomcat-NG: microsoft.public.visio.database.modeling

Yes, your technique works if your database isn't too complex (like a demo
database). However, as the complexity increases, this technique begins to
suffer from a few drawbacks - including:

1. What happens if Table B has a dozen or more columns? When you drag it
onto page one, you'll have to make room for this monstrous rectangle. Most
of the time, that's why I split the diagram into pages ... because I have
too many of those big rectangles to fit on one page.

2. The problem would be compounded if Table A has a dozen or so parent or
child relationships. I'd have to drag Table B thru Table n onto the page.
Again, having too many tables on a page is why I split it to begin with.

3. Having Table A appear on multiple pages with different sets of FK lines
to Table B thru Table n makes it hard to determine which is the
"official" Table specification. Is Table A on page one, with 4 child
tables correct ... or is Table A on page ten, with 2 child tables correct?

I finally adopted my technique (outlined below) to answer these drawbacks:

Answer to Drawback 1: instead of having to fit a big rectangle on the
page, I use just one line of text in a shape.

Answer to Drawback 2: multiple parent / child relationships appear in a
single shape (well, actually, two shapes since I have one for parents and
one for children).

Answer to Drawback 3: Table A appears only on one page of my ERD, so I
don't have to mentally merge the table appearing on multiple pages.

My technique may not be the "best" approach, but it does fit my more
complex database. It works for me. Sorry to take so many lines to describe
it.

Hi Chang Oh [MSFT], thanks for writing this:

You can drag any existing table from Tables window (Database > View >Tables) on to the diagram to make a second (or third, ..) graphical instance of the same
table. So, if you want to connect table A on page one with table B on page 2, you can either drag B onto page 1 to connect or A onto page 2 to connect.

Also note that creating a new graphical table instance will result in displaying all relationships that are already defined with other

tables on the page. To simply hide

 

[ST]

I think it might be easier to just enlarge the page size (in page setup/page
size) so you can fit all your schma in one page and let the Vision print it
into multiple pages.

Hi, Karsten,

Thanks very much for the information. Your experience and "tips" on how

to manage large schema will surely help a lot of folks.

--

Chang Oh
Visual Studio Enterprise Frameworks and Tools

This posting is provided "AS IS" with no warranties, and confers no

rights. Use of included script samples are subject to the terms specified

at http://www.microsoft.com/info/cpyright.htm
--------------------
From: Karsten Farrell <[email protected]>
Newsgroups: microsoft.public.visio.database.modeling
Subject: Re: How to connect tables when they are in different pages of the data model document?
Message-ID: <[email protected]>
References: <[email protected]>

<[email protected]>

<[email protected]>
X-Newsreader: MicroPlanet Gravity v2.60
Lines: 133
NNTP-Posting-Host: 67.117.210.172
X-Complaints-To: (e-mail address removed)
X-Trace: newssvr13.news.prodigy.com 1059757578 ST000 67.117.210.172 (Fri, 01 Aug 2003 13:06:18 EDT)
NNTP-Posting-Date: Fri, 01 Aug 2003 13:06:18 EDT
Organization: SBC http://yahoo.sbc.com
X-UserInfo1: [[PAPDONYRWMQQT[@JJ@NFXBWR\HPCTL@XT^OBPLAH[\RSAANVUEAE[YETZPIWWI[FCIZA^NBFXZ
_D[BFNTCNVPDTNTKHWXKB@X^B_OCJLPZ@ET_O[G\XSG@E\G[ZKVLBL^CJINM@I_KVIOR\T_M_AW_M[
_BWU_HFA_]
@A_A^SGFAUDE_DFTMQPFWVW[QPJN
Date: Fri, 01 Aug 2003 17:06:18 GMT
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.news2me.com!elnk-nf2-pas!elnk-atl-nf1!newsfeed.earthlink.net!prodig
y.com!newsmst01.news.prodigy.com!prodigy.com!postmaster.news.prodigy.com!newssvr13.news.prodigy.com.POSTED!34d3422d!not-f
or-mail
Xref: cpmsftngxa06.phx.gbl microsoft.public.visio.database.modeling:2983
X-Tomcat-NG: microsoft.public.visio.database.modeling

Yes, your technique works if your database isn't too complex (like a demo
database). However, as the complexity increases, this technique begins to
suffer from a few drawbacks - including:

1. What happens if Table B has a dozen or more columns? When you drag it
onto page one, you'll have to make room for this monstrous rectangle. Most
of the time, that's why I split the diagram into pages ... because I have
too many of those big rectangles to fit on one page.

2. The problem would be compounded if Table A has a dozen or so parent or
child relationships. I'd have to drag Table B thru Table n onto the page.
Again, having too many tables on a page is why I split it to begin with.

3. Having Table A appear on multiple pages with different sets of FK lines
to Table B thru Table n makes it hard to determine which is the
"official" Table specification. Is Table A on page one, with 4 child
tables correct ... or is Table A on page ten, with 2 child tables correct?

I finally adopted my technique (outlined below) to answer these drawbacks:

Answer to Drawback 1: instead of having to fit a big rectangle on the
page, I use just one line of text in a shape.

Answer to Drawback 2: multiple parent / child relationships appear in a
single shape (well, actually, two shapes since I have one for parents and
one for children).

Answer to Drawback 3: Table A appears only on one page of my ERD, so I
don't have to mentally merge the table appearing on multiple pages.

My technique may not be the "best" approach, but it does fit my more
complex database. It works for me. Sorry to take so many lines to describe
it.

Hi Chang Oh [MSFT], thanks for writing this:

You can drag any existing table from Tables window (Database > View

Tables) on to the diagram to make a second (or third, ..)
graphical instance of the same

table. So, if you want to connect table A on page one with table B on

page 2, you can either drag B onto page 1 to connect or A

onto page 2 to connect.

displaying all relationships that are already defined with other

tables on the page. To simply hide

rights. Use of included script samples are subject to the terms

 

[Karsten Farrell]

Hi ST, thanks for writing this:

I think it might be easier to just enlarge the page size (in page setup/page
size) so you can fit all your schma in one page and let the Vision print it
into multiple pages.

That too is a good idea, but also has a couple of drawbacks:

1. You have to be careful not to let anything of value stray into the
gutter area (the non-printing area between pages). For example, if you
drop an entity on the page (shover) and it automatically shoves another
entity (shovee) into the gutter, you won't see the shovee on the printout.

2. You still have relationship lines disappearing off the edges of pages
into never-never land. Unless you get out the scissors and scotch tape to
tack the resulting pages together (or you're lucky enough to own a
plotter), you won't know where a line that exits the right side of page 4
connects to a line coming in the left side of page 12 (I just made up the
page numbers, of course).

By the way, I'm not knocking Visio on this issue. Many of the packages
designed specifically for drawing ERDs suffer from this same problem ...
trying to fit a ton of entities in a 5-pound bag.