Many to Many Conundrum


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Many to Many Conundrum

  1. #1
    Ron Lustgarten Guest

    Many to Many Conundrum

    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.

  2. #2
    Cindy Winegarden Guest

    Re: Many to Many Conundrum

    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.



  3. #3
    Ronald Lustgarten Guest

    Re: Many to Many Conundrum

    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
    >
    >


  4. #4
    Cindy Winegarden Guest

    Re: Many to Many Conundrum

    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
    > >
    > >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center