Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Multiple relationships deep

Question asked by miw on Mar 10, 2010
Latest reply on Mar 11, 2010 by comment_1

Like • Show 0 Likes0
Comment • 9

Title

Post

I have been able to build my database efficiently using simple file relationship links when the relationship is only one step away. I always use the FM assign auto serial number of a table field to be my link to another table (because that ID is always unique and cannot be edited once committed). Now I am trying to create table which would link to all tables that may be multiple relationships deep; I cannot quite figure out how to do it. Here is my structure (I am noting only the linked fields and not all the other fields for the table):

TABLES: Country Den Type Inv

FIELDS: CountryID----------<CountryLink>--------<CountryLink

DenID-----------------<DenLink

TypeID---------???----<TypeLink

I am set with good links, layouts and portals for the Country, Den and Type tables. Linking the Type table to the Inv table is problematic. I was hoping I can solely establish the Type link to bring in all the predecessor table information for an Inv record. However, I cannot figure out how to make that link work in a layout because I need to pick the Type link that would incorporate the descriptive characteritstics (not the serial number IDs which actual form the link because they are not descriptive and since they are multiple tables away, a pop-up menu list doesn't help) of the Type, Den and Country names at one time (I can have many Countries linked to a Type; I can have many Types linked to a Den - so making a Type selection requires knowing the possible combinations to choose the unique Type that incorporates the desired Den and Country). Any ideas?

No one else has this question

Outcomes

9 Replies

philmodjunk Mar 10, 2010 2:30 PM
Mark Correct
Correct Answer
Can you post an example with some real values for each field? The abstract form of your post makes it difficult to understand.

One design question: If DenID is a unique serial number, why do you include CountryLink in the same relationship?

"making a Type selection requires knowing the possible combinations to choose the unique Type"

That's where you lost me. Combinations of what?
Like • Show 0 Likes0
Actions
miw Mar 11, 2010 6:06 AM
Mark Correct
Correct Answer
Phil, here is an example of real values:

TABLES:        Country                    Den                             Type                              Inv
FIELDS:           CountryID----------<CountryLink>--------<CountryLink
                                                            DenID-----------------<DenLink
                                                                                                 TypeID---------???----<TypeLink
EXAMPLES: Data Values linked to a Type record:
                        Country                    Den                             Type                              Inv
example 1:          USA--------------Dime-------------------Mercury
example 2:          Canada---------Dime-------------------Boat
example 3:          USA--------------Dime-------------------Barber
example 4:          USA--------------Quarter----------------Barber
example 5:          Euro--------------Dime------------------ Boat

Summary of Where I Am so far: The way my Types are linked are through the predecessor relationship files (Country and Den) serial IDs. To record a Type, I first must select a Country (only Countries that have pre-established links to a Den are available in the pop-up menu for this field in the layout). Once Country is selected, that then allows me to select a Den appropriate only for that Country (through a mirrored Den table). This works well since Countries can have multiple Dens and Dens can be in multiple Countries. Type descriptions can also be multiple ("Barber" or "Boat" in the examples above) but what makes each Type unique is not their description but their description plus their links to both Country and Den - thus each Type above is unique. (I test that each Type is unique by concatenating the Type description, CountryID and DenID into a string).

My Problem: I am stuck getting to the next phase where I want to link an Inv record to a Type. That is where I need to make a Type selection from the possible combinations of Country, Den and Type as noted above (I should have said a unique Type based on Type description, Country and Den). For example, I want a Inv record linked to the example 3 combination above - Barber as Type with Dime as Den and Country as USA. How can I get the layout to help me select the correct Type (showing a pop-up menu based on Type ID in the Inv layout only allows me to see "Barber" as my second field - but how do I know if its "Barber" from example 3 or example 4?). I do not think a concatenated string works (I use the serialIDs for all links so they are not descriptive, plus if I used a more descriptive concatenated field, that would not be a dynamic link if the description for Country or Den was subsequently edited).

I seem so close yet I am stuck on this one....

Any help you can provide would be greatly appreciated!
Like • Show 0 Likes0
Actions
comment_1 Mar 11, 2010 6:21 AM
Mark Correct
Correct Answer
It would be much better if you explained what this is really about - or at least give an example that makes sense. I have no idea what a den is (assuming your solution is not about the sleeping arrangements of lions), and having dens of type Barber or Boat certainly doesn't help. Neither does a cryptic abbrevation like Inv (Invoices? Inventory? Investments?).

MIW wrote:
Countries can have multiple Dens and Dens can be in multiple Countries.

Not according to your diagram: there is a one-to-many relationship between Country and Den.
Like • Show 0 Likes0
Actions
miw Mar 11, 2010 9:41 AM
Mark Correct
Correct Answer
A "Den" is short for a coin denomination. "Inv" is for inventory.

As for Countries having multiple Dens and Dens having mulptiple Countries, I should have been more clear. Effectively, as you more correctly note, there is a one-to-many relationship between Country and Den. This is because each Den is unique based on the Den and Country link (in my examples, although there appear to be many "Dime" Dens linked to multiple Countries, that is not the case because each Den is unique based on the Den and Country link. I my examples, I have a unique Den representing USA and Dime which is linked to mulitple Types. I also have a unique Den for Canada and Dime and yet another unique Den for Euro and Dime - both of which are ulitmately linked to a Unique Type (Canada-Dime-Boat and a different Type for Euro-Dime-Boat).
Like • Show 0 Likes0
Actions
comment_1 Mar 11, 2010 9:55 AM
Mark Correct
Correct Answer
OK, that's a good start. So you have (or should have): Countries -< Denominations >- Types Now, how does Inventory figure in this? Assuming this is a coin collection, why not simply add a Quantity field to the Denominations table?
Like • Show 0 Likes0
Actions
miw Mar 11, 2010 11:05 AM
Mark Correct
Correct Answer
Inv[entory] is a table for coins in the collection. I can't just add a quantity field to the Denominations table because I first need to reference a Type to it so that each coin has a unique Type, Denomination and Country attached to it. It can't simply add a quanity field to the Type table because each coin has specific attributes (grade, location, etc.) that are unique to the coin and I can multiple coins for each Type. I built the linked tables because each table has plenty of underlying data specific to Country, Denomination and Type that I would want each coin in the Inv table to be able to reference.
Like • Show 0 Likes0
Actions
comment_1 Mar 11, 2010 11:42 AM
Mark Correct
Correct Answer
You are starting to lose me again, I'm afraid. I think you are saying that each coin is a record in the Inventory table? If so, your relationships should look like this:

Countries -< Denominations >- Types
                   |
                   ^
               Inventory

Note that each denomination has one country and one type - so by assigning a coin to a denomination, those two are given.
Like • Show 0 Likes0
Actions
miw Mar 11, 2010 11:51 AM
Mark Correct
Correct Answer
Ok - I see your point as to the proper relationship link and now I am on the right track as to the tables and relatinships. But how on the Inventory layout do I pick the requisite Denomination link that is also appropriately linked to both Country and Type (I need to be able to see a main description from all three linked tables to do the job by choosing the right combination of all three as in any of the examples shown)? That's where I get stuck.
Like • Show 0 Likes0
Actions
comment_1 Mar 11, 2010 12:10 PM
Mark Correct
Correct Answer
This is more of a user interface issue than a relationships one. My preferred method would be to pop up a new window with a list view of the Denominations table (with related fields from the other two tables), and select from there. But there are alternative methods, e.g. a conditional value list or a filtered portal.

Coming back to the relationships again: I've taken it as given that you keep a "catalog of all possible coins" in the Denominations table. But perhaps you should consider an alternative:

           Denominations
                |
                ^
Countries -< Inventory >- Types
Like • Show 0 Likes0
Actions

Multiple relationships deep

Title

Post

Outcomes

Related Content

Recommended Content