more formal definition of database & dynamic dropdowns

[edrosenthal350859]

Situation:
I have a database containing states, zipcodes, cities, counties etc.

I have created a table with states and stateId -
the stateId is the Primary Key (int autoincrement), although the States data itself
could act as a PK here as well.
The RecordSet for this table contains all the fields, State and StateId.

Another table t_cities contains City, CityId,County,State,StateId.
The PK is the CityId ( integer, auto increment).
To implement Foreign Keys on these tables I created these tables
as InnoDB types.
I wasn't sure if FK were necessary though,
because there didn't seem to be a mentioning of it anywhere, yet i thought it wouldn't hurt.
I created a Foreign Key in the t_cities table, based on the State relationship in this table to the State table; FK_t_cities(State).
I created two recordsets with all the fields, one RS for each table.
I created two form->list/menu fields, one for the states and one for the cities.
The state field is dynamic from the recordset for states.
In the section of the document where it says ( page 7 on my pdf output)
"Choose Create Dynamic Array"
#4 says from the Recordset menu choose the Child Table- In this case i have the two recordsets to choose from, so I choose the "Child" or in this case the "rs_cities" recordset.
#5 says From the Parent Id field menu, choose the ParentId column.
Here I have several choices but am confused as to which one is the proper choice.

Question -
DO i choose a column that is a Primary Key for this table, or do i choose a column that is a Foreign Key to the State Column, Or is it something else altogether?
Does the column that I choose have to be unique or is it simply a data key into the state table with no unique or PK or FK features?
#6 is going to be a unique key of this table, apparently.
Going back to the first page "Configuring your database"
in 3.
"The Child table must include an ID column, A Secondary key to the Parent table's ID, and one for the content."
And I believe here was my first confusion. I wasn't sure from rereading this that you are required to have 3 columns or 2 columns in the child table.

The phrase "a Secondary Key to the Parent table Id" I first took to be a description of the first phrase "The Child Table...", not seeing that there are three columns required in this child table. I take it then there are 3 columns required? And what is the formal definition of these columns? One of them is a FK or not?
An even more direct question is, what columns are necessary in the City table to allow the field to be populated?
Additional question: the City table and record set will also be used as the source for a third drop down, the county,
what do the first two tables ( state and city) need in order for this third dropdown to work properly?
Further questions include:
When you are creating the Dynamic List Menu and you are given the choice to
put in the "Values" and in the Lables areas; in Values do you choose the primary key or
is it going to be used in another way in another dialogue further in the process? Must it be
a PK or will it be used as a FK somewhere else?

[Jason Byrnes]

your cities table does not need to include the state column, only the stateid column.


The parent recordset would need to be:
SELECT statesName, stateId FROM states


in the first list, the value will use the stateId column, the label will be the statesName column.


This way t he user will see the state name in the list, but the list will pass the state ID.


The child Recordset will be:
SELECT StateId, CityId, City FROM t_cities


When creating the Dynamic Array, the Parent ID is the column that relates to the value being passed by the Parent select list, in this case StateId. The Child ID is the column that will be used as the value for the child list (CityId). Child Text is the column to be used as the label for the child list (City).