title: MySQL relations for country region and town tables
body: <p>I am trying to figure out the best method to relate country, region and town tables.</p>
  <p>On my website I want the user to be able to just enter a town. Then optionally country and region, both of which will be required to be entered or not at all.</p>
  <p>Currently my tables are as such</p>
  <blockquote>
  <p>tbl&gt;User has townID (FK)</p>
  <p>tbl&gt;town has id(PK) townName regionID(FK DEFAULT NULL)</p>
  <p>tbl&gt;region has id(PK) regionName countryID(FK NOT NULL)</p>
  <p>tbl&gt;country has id(PK) countryName</p>
  </blockquote>
  <p>I thought to possibly further spit the user to town relation to:</p>
  <blockquote>
  <p>tbl&gt;User has locationID (FK)<br>
  tbl&gt;location has id (PK) townID(FK) regionID(FK) countryID(FK)</p>
  </blockquote>
  <p>But I think that is unnecessary and just further complicates the issue?</p>
  <p>The country database is already populated. I intend to build up my own references of town &gt; region &gt; country relations as entered by users. So if a user enters a town with no region and country then it is entered into tbl&gt;town without a regionID if there isn't already a town with the same name without a region ID. This is same for a town where a region and country ID has been entered by the user. Only I check that there isn't already a town &gt; region &gt; country relation that already exists before entering. Later on in the development of the site I will be providing Ajax suggestions for country/region based upon the town entered by a user.</p>
  <p>So to the questions:</p>
  <p>I can envisage pitfalls with this such as duplicate data or data possibly being overwritten. Is there a better way to construct the tables to fit in with my desired methods?</p>
  <p>This might get answered by the prior question: but is there anything I can do to reduce the PHP processing of the tables. Obviously I'd prefer to just insert with one PHP statement but I think there are too many caveats to do it at once.</p>
  <p>Also as the users town entry may be null and may or may not contain a foreign key reference to a region how is it best to create a View that takes that into consideration?</p>
  <p>As it will be hosted I would rather not be using MySQL functions.</p>
  <p>Please let me know if you need any clarification. I really want to get this right the first time before continuing, so your help will be invaluable.</p>