Forms technique for instantiating a relationship involving searching

  • Thread starter Thread starter Gary Schuldt
  • Start date Start date
G

Gary Schuldt

I need a standard design approach to this problem pattern:

Let's say I have two tables tA and tB with a n-n relationship trelAB between
them (with FK's to both tA and tB). The object of this user session is to
create instances of trelAB.

Example: tA is a table of photographs (tPhoto) and tB is a table of Plants
(tPlant). trelAB keeps track of which Photos depict which Plants
(trelPlantPhotoDepiction).

Scenario 1: I take a digital pic and I want to record which plants in
tPlant are depicted in that Photo. There are so many plants that I need to
search for the correct plant (i.e., I don't automatically know the PlantID);
and it may not even be in the database. (In those cases where I know the
ID--i.e., I've already tagged the plant--I'll just make note of the ID's of
the Plants in the pic and enter them and be done with it.)

Scenario 2: I enter a new plant into tPlant and want to link it to any
photos I have of it. I will have to search for the photos based on some
descriptor info in tPhoto, and in many cases will even have to view the
photo just to make sure it depicts this plant. There may be no Photos of
this plant currently in the database.

These two scenarios seem symmetric: It's an n-n relationship between them,
and, no matter which one I'm starting with, I have to do a human search to
make a decision about whether or not I can establish an instance of the
relationship to the other.

In my mind, this cries out for a standard design pattern that I could reuse
as needed. I have the rudimentary elements of it in my mind, but I need
help pulling it together! I'm sure someone has this design pattern in their
bag of tricks!

Thanks in advance.

Gary
 
The first scenario you outline can be done in ms-access without any code.

You should "think" of your table trelAB is not some junction table, but
simply as:

Hum, I need a list of plants that are in the photo. So, really, what we have
is a list of plants that belong to a photo. (you can call this a n-n table,
or some fancy junction table..but really, at the end of the day...you need a
list of plants that belong to the picture).

In reading your first explanation..that is exactly what you are getting at!:
Example: tA is a table of photographs (tPhoto) and tB is a table of Plants
(tPlant). trelAB keeps track of which Photos depict which Plants
(trelPlantPhotoDepiction).
Click to expand...

The solution is to simply built a nice form with the fields that you need on
it from tA (date photo was taken, who took the photo, perhaps location,
perhaps time etc.). In fact, a good design would also have a "link" to the
actual file on your hard disk,a nd it is trivial to make the form "display"
this photo (you DO NOT WANT TO store the photos in the database...it
completely un-workable).

After you built this nice form, we now need the ability to "add" each plant
in to this nice form. The solution here is to use a sub-form. In fact, that
sub-form ONLY needs to display the plantID field from trelAB. So, just make
a simple sub-form that is bound to the trelAB. If you set the child/master
link field, the each time you time in a new plantID, the correct parentid
(photoID) will be set in this n-n table (it does not have to be
displayed...and further..and no code is required).

Of course, it is a real pain to type in the tB (plant id). So, in place of
using a text box, just use a combo box that searching on the tb Plant
Description, but STORES THE ID. A combo box can do this! So, just use the
combo wizard to build such a combo for you.

So, really, I don't see any need for a design pattern when the whole thing
can be done in a matter of minutes. Anyone who can use the wizards can do
this..and ...no code is needed.

Well, ok, the only code you need would be some to load up the cool picture
that you no doubt have on the disk drive somewhere (but as mentioned you
MUST NOT store those pictures in the database).
Scenario 2: I enter a new plant into tPlant and want to link it to any
photos I have of it. I will have to search for the photos based on some
descriptor info in tPhoto, and in many cases will even have to view the
photo just to make sure it depicts this plant. There may be no Photos of
this plant currently in the database.
Click to expand...

You again, simply make a form for the plants, and the sub-form is a form to
select what picture it belongs to. Again, this sub form will be based on
trelAB, but now you are doing the exact reverse of the first case.
In my mind, this cries out for a standard design pattern that I could reuse
as needed. I have the rudimentary elements of it in my mind, but I need
help pulling it together! I'm sure someone has this design pattern in their
bag of tricks!
Click to expand...

Actually, most of use don't have this design ready to go. However, since a
few mouse clicks, and a few wizards later .... all of what you need can be
done with one hand on the mouse. So, ms-access has this ability built in.

If the above needed a large library of code to accomplish..then yes..a
reusable class object, or even simply a library of code to accomplish this
might make sense..but as it stands now...ms-access works this way without
any code as is.

I write class objects all the time in ms-access. Your above scenario as I
see it would not benefit from creating a re-usable code object.
 
Albert,

thanks for the detailed reply. There's a lot of information I can use in
what you wrote!

1. I don't mind coding, and the forum has taught me a lot about VBA and
queries and such, but it's good to let Access do as much of the work as
possible, I agree.

2. I don't think of trelAB as "some junction table". To us professional
data modelers it's just a tabular representation of a relationship . . . in
this case between Photo and Plant, where a "Photo Depicts a Plant". (And
someone else could tell that trelAB is a relationship by its field content,
since it contains more than one ID.)

I don't know who first invented the term "junction table"--I think it may
have arisen as early as the late 60's with the then-called "network" model
(as distinguished from the hierarchic model) of data. Too bad it has
persisted: The concept seems to scare people!

3. re: "it is trivial to make the form "display" this photo"

Perhaps so, but I haven't used that feature in Access yet. It would be good
to do that, though, since we're preparing for a garden tour and plan to have
PC's deployed in the garden with the detailed botanical information
available via a wireless LAN for people on the tour. Since not all the
plants will be blooming on July 31st, it would be good to be able to display
a picture of their bloom on the computer. I wonder if Access lets me
control which program will display the photo?

4. re: "use a combo box that searching on the tb Plant Description, but
STORES THE ID"

Actually, zeroing in on the correct Plant (ID) is the time-consuming part of
this process. The person entering / looking at the photo must look at the
picture, visually interpret what's there, and translate that to some textual
characterization of the plant. Typically this will be the full or partial
botanical name (genus / species / variety), or perhaps the common name. The
database currently keeps track of all this, and I have already built search
screens that help the user isolate the specific plant they are interested
in, including web links.

However, as I said, the plant may not yet be in the database. Then it would
then need to be added, and I already have the functionality to do this.

What I need the "design pattern" for is this: Within the environment of
dealing with a particular photo, a "calling structure" that invokes the
search and display sequence of screens I've already designed for other
purposes and yields either a PlantID to store into the subform you suggest .
.. . or yields a "no hit" situation.

I have a feeling it would make use of modal forms to maintain the context of
trying to locate the correct plant in order to place its ID into the Photo
subform. I do have all the pieces to the puzzle, and I guess I can figure
out how to put them together.

5. re: "you DO NOT WANT TO store the photos in the database"

Yes, they are already in other files on the LAN, and the garden PC's will be
wirelessly connected to the server on which the pics are stored (in JPG
format). So I definitely would not duplicate them within the database!

6. re:
You again, simply make a form for the plants, and the sub-form is a form to
select what picture it belongs to. Again, this sub form will be based on
trelAB, but now you are doing the exact reverse of the first case.
Click to expand...

Yes, I understand it is the exact reverse. Fortunately, I already have a
Plant form for entering the plant (textual) data. I need to think about the
user scenario a little more to see if I need a new Plant form to use in
adding photo links. . . or just add a photo subform to the existing
Add/Modify Plant info form. More analysis on my part is needed here, I
think.

7. re:
I write class objects all the time in ms-access. Your above scenario as I
see it would not benefit from creating a re-usable code object.
Click to expand...

I have already used the form sequence "search for plant, and, if not there,
add, and, in either case, give me the PlantID" twice in the application in
two different user contexts, and this will be the third use of it. So I
would benefit from a reusable pattern, since I am determined to duplicate as
little coding as possible!

I guess, at this point, the pattern I'm seeking might only be one of concept
or approach and not necessarily of a "library of code", although I have
written most of the code already (forms and VBA event handlers, etc).

Albert, I realize that this may all be too vague to expect a reply to, but
at least I hope it has been somewhat entertaining!

Regards,

Gary
 
Back
Top