Relationships from sub-sub-subset to super-super-superset

[Chappy.Cole]

I have a scenario that which requires at least 4 tables which can be represented as such:

Continent (which is a superset of)

Country (which is a superset of)

State (which is a superset of)

City

I would like to be able to create relationships, value lists and layouts that enable the user to view the information from the top (Continent) all the way down (to City) (succeeded here already) *AND* from the bottom (City) all the way up (to Continent) (failed here). My failure is not that I cannot figure out how to accomplish the task, but rather that the solutions that I can come up with seem flawed. My instincts tell me that there must be a clean method that I am currently unaware of.

If there's a white paper or some such document that explains this scenario with good examples, that would be great.

Thanks,

Chappy

[comment]

I am not sure where your difficulty lies: a city has a parent state related to it. The state, in turn, has a parent country, and the country has a parent continent. To view these, all that's required is to place the related fields on the layout of Cities.

BTW, the terms subset/superset do not apply to the described situation, IMHO.

[Chappy.Cole]

Ok. After further tinkering I think I have a more refined inquiry regarding my problem.

If one has a group of tables like my Continent->Country->State->City example, where all Country records relate to exactly 1 Continent record, and so on down the line, is there a standard rule for the use of key fields?

What I have now is:

--> Continent::continentId

--> Country::countryId, fk_continentId

--> State::stateId, fk_countryId

--> City::cityId, fk_stateId

However, another possibility that came to mind, and would solve my problem, is:

--> Continent::continentId

--> Country::countryId, fk_continentId

--> State::stateId, fk_countryId, fk_continentId

--> City::cityId, fk_stateId, fk_countryId, fk_continentId

The reasoning is that when I need to access data from, let's say, the State table and some other table that is only related to the Continent table, I have fk_continentId in each record of the State table which enables me to make the various relationship connections that I need.

I see the first method as a cascade-like set of tables, each with a primary key field and all but the starting table (Continent) with exactly 1 foreign key field relating that table backwards to it's superior table. The second method, although it would solve the problem that I am having, seems unclean to me due to the "extra" foreign key fields. Is is common-practice/safe/normal to have multiple foreign key fields in a table?

This brings me to my final comment. "Comment" (the "name" of the user who commented on my first post) said that he/she thought that my example did not meet the Set theory criteria. I would like to know how you ("Comment") would characterize my example of Continent-County-State-City? What about it do you think is not consistent with Set theory?

[Vaughan]

Each table needs a primary key which is an auto-entered serial number that cannot be modified after entry. I'd name them explicitly with the table name, eg, ContinentID, CountryID, StateID, CityID.

Most of the other tables also need foreign key fields to contain the key of the records they relate to.

Country table: ContinentID

State table: CountryID

City table: StateID

Once you have these primary keys and foreign keys set up, arrange the table occurrences in the relationships graph and join the tabs and slots together:

Continent -> Country using ContinentID

Country -> State using CountryID

State -> City using StateID

[comment]

a cascade-like set of tables, each with a primary key field and all but the starting table (Continent) with exactly 1 foreign key field relating that table backwards to it's superior table.

That is the correct approach.

Once you define all the required relationships in a Parent -< Child -< Grandchild cascade, you shouldn't have any problems showing the "lineage" of any record in the chain. Each record, in any table, can access related information from any other table in the group - "upstream" as well as "downstream".

"Comment" (the "name" of the user who commented on my first post) said that he/she thought that my example did not meet the Set theory criteria.

That's not what I said. I said the terms "subset" and "superset" do not apply here. In set theory, set B is a subset of set A when every element of B is also an element of A. Thus, the countries in Asia are a subset of countries - but Countries are not a subset of Continents.