more formal definition of database & dynamic dropdowns
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?