close ad
 
Important WebAssist Announcement
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

more formal definition of database & dynamic dropdowns

Thread began 11/25/2009 12:02 pm by edrosenthal350859 | Last modified 11/25/2009 3:58 pm by Jason Byrnes | 2535 views | 1 replies |

edrosenthal350859

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?

Sign in to reply to this post

Jason ByrnesWebAssist

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).

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...