>I have a two part question. Here's the scenario. We are using what I call bridge tables. They keep track of a “one to many” relationship without duplicating information in the child table. The bridge table usually only contains two or three fields. In this scenario it will contain three, two unique ID fields and a third used to distinguish between the different types of child records.
>
>Let’s say we have a COMPANY, BRIDGE and a CONTACT table. Since a company can have contacts that perform more than one job function, we don’t want to duplicate any of the contact information in the child table, so in the BRIDGE table we store the company ID, contact type, and contact ID. Therefore we have a “one to many” relationship between the COMPANY and BRIDGE table, and a “one to one” relationship between the BRIDGE and CONTACT table.
I think you mean "many-to-one",
between BRIDGE and CONTACT; a CONTACT can have a relationship with "many" companies (eg. a salesman, supplier, etc.)
COMPANY ->> BRIDGE <<- CONTACT
(However, there is a "one-to-one"
from BRIDGE
to CONTACT)
>I usually set complicated relationships in the data environment, but in this case I have to set the relationships on the fly depending on certain properties. In this scenario which relationship should be set first programmatically, or does it even matter?
You basically have 2 "views"; the setting of relationships is based on where you are "coming from"; eg.
COMPANY ->> BRIDGE -> CONTACT
or
CONTACT ->> BRIDGE -> COMPANY
(The actual mechanics of SET RELATION can be set in any order; ie. COMPANY to BRIDGE; then BRIDGE to CONTACT ... or BRIDGE first, etc.)
>My second question is about adding records in this same situation. All of the tables are optimistically table buffered. When I need to add a new contact, do I add the BRIDGE table record first or the CONTACT record?
A BRIDGE record "needs" a COMPANY record and a CONTACT record; therefore, a COMPANY and CONTACT record must exist before adding a BRIDGE (from a referential integrity point of view).
BTW, frequently, in these types of scenarios, there is no CONTACT and COMPANY table; there is only a "Party" table ... and "relationships" between Parties are affected using a "party relation" (ie. bridge) record that relates from one Party back to another party; eg.
Party ->> Party Relationship -> (back to Party)
Another approach employs a "Party Role" table, which describes the "roles" a Party can play. In this case, the "relationships" are formed between the Roles, and not directly between Parties; eg.
Party ->> Roles ->> Role Relationship -> (back to Role)
>I’ve noticed that when I just attach an existing contact record to an existing company, I have to perform a “GO TOP” in the BRIDGE table before the new record appears. Even with a form refresh the new BRIDGE record doesn’t appear in the grid unless I perform a “GO TOP IN BRIDGE” or simply move the record pointer.
Generally, I've found that in order to "sync" a grid, one needs to perform a
..MyGrid.SetFocus()