Hi folks,
My first post here
I am currently in the process of building my first serious DB (generic customer orders). I am beginning by building the tables for storing customer addresses.
My professional background is as a data/database/quality management analyst in Finance and I am generally very picky when it comes to data validation and definitely subscribe to the "no data is better than bad data" ethic.
So all this leads to me trying to create an address system where data is fully validated at table level while I want forms to be dynamic so that, if you selected 'United Kingdom' as the country, you will get a filtered options list for cities currently stored in the DB belonging to the United Kingdom.
The actual form query through event handling is not a problem for me (I should at this point perhaps state that I have used PHP/SQL a fair amount, have built dynamic websites and I would consider myself pretty much expert in Excel, including VBA for Excel). However, I dont know if I am entirely sure of the table structure for this.
I have thought it through and have come to the conclusion of: -
1) A table for countries with an autonumber PK
2) A table for cities with three fields - Autonumber PK, City and CountryID (FK). The City and CountryID fields would be a compound index (no duplicates).
3) A table for cities with three fields - Autonumber PK, Region and CountryID (FK). The Region and CountryID fields would be a compound index (no duplicates).
This would allow for the form to requery on change events to return only the relevant data. Where no city or region exists for that country, the user would be given the option of opening a form that will allow them to edit the relevant table to add the missing data, then the parent will requery on close.
Hoping I have explained that properly, does that make any sense?
My first post here
I am currently in the process of building my first serious DB (generic customer orders). I am beginning by building the tables for storing customer addresses.
My professional background is as a data/database/quality management analyst in Finance and I am generally very picky when it comes to data validation and definitely subscribe to the "no data is better than bad data" ethic.
So all this leads to me trying to create an address system where data is fully validated at table level while I want forms to be dynamic so that, if you selected 'United Kingdom' as the country, you will get a filtered options list for cities currently stored in the DB belonging to the United Kingdom.
The actual form query through event handling is not a problem for me (I should at this point perhaps state that I have used PHP/SQL a fair amount, have built dynamic websites and I would consider myself pretty much expert in Excel, including VBA for Excel). However, I dont know if I am entirely sure of the table structure for this.
I have thought it through and have come to the conclusion of: -
1) A table for countries with an autonumber PK
2) A table for cities with three fields - Autonumber PK, City and CountryID (FK). The City and CountryID fields would be a compound index (no duplicates).
3) A table for cities with three fields - Autonumber PK, Region and CountryID (FK). The Region and CountryID fields would be a compound index (no duplicates).
This would allow for the form to requery on change events to return only the relevant data. Where no city or region exists for that country, the user would be given the option of opening a form that will allow them to edit the relevant table to add the missing data, then the parent will requery on close.
Hoping I have explained that properly, does that make any sense?