With my trial period about to expire, I made the plunge and have my very own license. Now I'm committed, but not at all sure that I''m going to get my head around all this stuff. I get the advantages of relational design for storing information in one location and drawing it together through relationships and my current attempt works well for viewing my data once they've been stored in their various tables. Data entry, on the other hand, is causing me all kinds of headaches.
Say I have a set of tables related as follows:
Countries::pk_Country_ID ---> States::fk_Country_ID||States::pk_State_ID ---> Cities::fk_State_ID||Cities::pk_City_ID ---> Destinations::fk_City_ID
The Destinations table is necessary because, while all cities are destinations, not all destinations are cities (i.e., Disneyland, Grand Canyon National Park,...).
The primary interface is laid over the Destinations table and I would like to be able to:
1. Retrieve and view, but also perform data-entry through this layout.
2. Automate data entry, linking the relationship chain (destination -> city -> state -> country) according to the current
context (If the current record relates Durango -> Colorado -> USA, adding to City should be CityName -> Colorado -> USA)
3. Automatically create a destination record when a city record is created, each with the same Name-field contents.
I've been trying to script this, but linking in a new city entry up the relationship chain from the perspective of the destination layout is...well, yeah,... I don't know what it is except confusing. Am I going about this in the wrong way?
Thanks for any advice, including reading lists!