Many to Many Conundrum

[Ron Lustgarten]

I've a data model with several many to many relationships. For
simplicity, consider three tables, Projects, Investigators,
Technologies. Each of the latter two has a many-to-many relation to the
first (and to each other). I've set these up in a dbc (VFP 6 sp3),
along with the necessary junction tables, persistent relations, etc.
I've also created a single View that contains all the data I want to
have available for users to query, and I'm working on a form (designed
from the "Interactively Build a Select Statement" form in Solutions)
with that View in its data environment. The user will be querying the
view with Investigators.name and/or Technologies.name, and will be
generating a small subset of the records. The user will be looking for
a particular project, represented by, say, project.name, but will only
be able to recognize it by spotting the proper combination of, say,
Investigator.name and Technology.name.

This can be done by displaying the query results in a single grid. But
that's not pleasing, since it will give multiple lines for any project
for which there is more than one Investigator and/or more than one
Technology.

I'm a novice, trying to learn by developing a needed app. I'm seeking
general advice on approaches to this situation. I realize that I can
display just project.name in a grid, and technologies.name and
investigators.name in other controls, with project.name as a parameter
for a SQL statement as the recordsource (or rowsource) of the other
controls. When the user scrolls through the project.name control, the
other controls will refresh. That is how I want the form to work, but
is my suggestion the best way to accomplish it? It seems like I'd be
querying the underlying tables twice, once to create the cursor
containing the data, and again to display the data the way I want. I
could put all the tables in the form environment, instead of just the
view, but then I'm dealing with a complex sql statement every time I
want to experiment with the control rowsource.

The many-to-many implementation in Tastrade, while helpful, is not
analogous to this situation, where the user needs to identify the
desired datum in a parent table by spotting related data from two (or
more) child tables.

I suppose this comes down to a form design question, though I also have
the vague feeling I'm missing something fundamental. I want to get this
right, because ultimately I'll want to create a class from the form.
I'd appreciate comments or suggestions. Thanks, Ron.

[Cindy Winegarden]

Ron,

Your idea of putting the Project name in a control that the user can scroll
through is good. You might try a list box or combo box though. That means as
the user steps through the list of projects, all combinations of
Investigators and Technologies will show in a continually refreshing grid,
right?

Set the source for the list box to the Projects table. Create a
parameterized view of the junction table (with the Investigators and
Technologies expanded) (look at NoDataOnLoad) and requery it later when the
Project has been chosen.

Whether the tables/views are in the form's environment or in the default
data session and already open isn't all that important. Some people open
everything at the beginning of an application and keep it open, others open
and close as they need data.

This all sounds pretty sensible to me. You're concerned about time then? Is
your data in VFP tables or a client/server database? How many records in the
table? Do you have indexes on Projects, Investigators, and Technologies?
With proper indexing and tables of a "reasonable" size you should have
acceptable speed. Don't forget, tables that appear large to you (100,000
records) are still peanuts to the Fox.

--

Cindy Winegarden MCSD, MVP
cindy.winegarden@mvps.org


"Ron Lustgarten" <rkl12@columbia.edu> wrote in message
news:3B40C917.9CDE8BB8@columbia.edu...
> I've a data model with several many to many relationships. For
> simplicity, consider three tables, Projects, Investigators,
> Technologies. Each of the latter two has a many-to-many relation to the
> first (and to each other). I've set these up in a dbc (VFP 6 sp3),
> along with the necessary junction tables, persistent relations, etc.
> I've also created a single View that contains all the data I want to
> have available for users to query, and I'm working on a form (designed
> from the "Interactively Build a Select Statement" form in Solutions)
> with that View in its data environment. The user will be querying the
> view with Investigators.name and/or Technologies.name, and will be
> generating a small subset of the records. The user will be looking for
> a particular project, represented by, say, project.name, but will only
> be able to recognize it by spotting the proper combination of, say,
> Investigator.name and Technology.name.
>
> This can be done by displaying the query results in a single grid. But
> that's not pleasing, since it will give multiple lines for any project
> for which there is more than one Investigator and/or more than one
> Technology.
>
> I'm a novice, trying to learn by developing a needed app. I'm seeking
> general advice on approaches to this situation. I realize that I can
> display just project.name in a grid, and technologies.name and
> investigators.name in other controls, with project.name as a parameter
> for a SQL statement as the recordsource (or rowsource) of the other
> controls. When the user scrolls through the project.name control, the
> other controls will refresh. That is how I want the form to work, but
> is my suggestion the best way to accomplish it? It seems like I'd be
> querying the underlying tables twice, once to create the cursor
> containing the data, and again to display the data the way I want. I
> could put all the tables in the form environment, instead of just the
> view, but then I'm dealing with a complex sql statement every time I
> want to experiment with the control rowsource.
>
> The many-to-many implementation in Tastrade, while helpful, is not
> analogous to this situation, where the user needs to identify the
> desired datum in a parent table by spotting related data from two (or
> more) child tables.
>
> I suppose this comes down to a form design question, though I also have
> the vague feeling I'm missing something fundamental. I want to get this
> right, because ultimately I'll want to create a class from the form.
> I'd appreciate comments or suggestions. Thanks, Ron.

[Ronald Lustgarten]

Cindy, thank you. I'm not totally clear on your second paragraph, since
the user won't know what project they really want until they see the
corresponding mix of Investigators and Technologies. Like looking for a
book title where you know, say, one syllable of one author's last name
and maybe one keyword (but not all) for the subject matter, but you
haven't a clue about the actual title. It took me a while, but I
realized that the datum I'd want to scroll is the book title. The one
I'd never recognize! Because I'll know it's the right one only by the
combination of the other two - the author's full name and all the
keywords. So yes, I like your idea of having the Project name in a list
box and the other two in a grid.

You'll laugh at this: I'm talking fewer than 3 thousand projects,
growing at probably less than a thousand a year. Strictly VFP tables on
a LAN. So speed is not the issue. Just trying to learn healthy
habits. Many thanks, Ron.

Cindy Winegarden wrote:
>
> Ron,
>
> Your idea of putting the Project name in a control that the user can scroll
> through is good. You might try a list box or combo box though. That means as
> the user steps through the list of projects, all combinations of
> Investigators and Technologies will show in a continually refreshing grid,
> right?
>
> Set the source for the list box to the Projects table. Create a
> parameterized view of the junction table (with the Investigators and
> Technologies expanded) (look at NoDataOnLoad) and requery it later when the
> Project has been chosen.
>
> Whether the tables/views are in the form's environment or in the default
> data session and already open isn't all that important. Some people open
> everything at the beginning of an application and keep it open, others open
> and close as they need data.
>
> This all sounds pretty sensible to me. You're concerned about time then? Is
> your data in VFP tables or a client/server database? How many records in the
> table? Do you have indexes on Projects, Investigators, and Technologies?
> With proper indexing and tables of a "reasonable" size you should have
> acceptable speed. Don't forget, tables that appear large to you (100,000
> records) are still peanuts to the Fox.
>
> --
>
> Cindy Winegarden MCSD, MVP
> cindy.winegarden@mvps.org
>
>

[Cindy Winegarden]

Ron,

Then SELECT a combination of Investigators and Technologies into your
listbox and then requery the many-to-many view as you step through that. If
you set up the indexes right you should have plenty of speed on these small
tables.

I'm in an academic setting and I know the types of things you're dealing
with.

--

Cindy Winegarden MCSD, MVP
cindy.winegarden@mvps.org


"Ronald Lustgarten" <rkl12@columbia.edu> wrote in message
news:3B41CF75.10D825E5@columbia.edu...
> Cindy, thank you. I'm not totally clear on your second paragraph, since
> the user won't know what project they really want until they see the
> corresponding mix of Investigators and Technologies. Like looking for a
> book title where you know, say, one syllable of one author's last name
> and maybe one keyword (but not all) for the subject matter, but you
> haven't a clue about the actual title. It took me a while, but I
> realized that the datum I'd want to scroll is the book title. The one
> I'd never recognize! Because I'll know it's the right one only by the
> combination of the other two - the author's full name and all the
> keywords. So yes, I like your idea of having the Project name in a list
> box and the other two in a grid.
>
> You'll laugh at this: I'm talking fewer than 3 thousand projects,
> growing at probably less than a thousand a year. Strictly VFP tables on
> a LAN. So speed is not the issue. Just trying to learn healthy
> habits. Many thanks, Ron.
>
> Cindy Winegarden wrote:
> >
> > Ron,
> >
> > Your idea of putting the Project name in a control that the user can
scroll
> > through is good. You might try a list box or combo box though. That
means as
> > the user steps through the list of projects, all combinations of
> > Investigators and Technologies will show in a continually refreshing
grid,
> > right?
> >
> > Set the source for the list box to the Projects table. Create a
> > parameterized view of the junction table (with the Investigators and
> > Technologies expanded) (look at NoDataOnLoad) and requery it later when
the
> > Project has been chosen.
> >
> > Whether the tables/views are in the form's environment or in the default
> > data session and already open isn't all that important. Some people open
> > everything at the beginning of an application and keep it open, others
open
> > and close as they need data.
> >
> > This all sounds pretty sensible to me. You're concerned about time then?
Is
> > your data in VFP tables or a client/server database? How many records in
the
> > table? Do you have indexes on Projects, Investigators, and Technologies?
> > With proper indexing and tables of a "reasonable" size you should have
> > acceptable speed. Don't forget, tables that appear large to you (100,000
> > records) are still peanuts to the Fox.
> >
> > --
> >
> > Cindy Winegarden MCSD, MVP
> > cindy.winegarden@mvps.org
> >
> >