Database design - Town/City/Region/Country

BigSneeze

Hello.

I am trying to design a database that will allow people to add themselves to a list and specify their location. The location will either be a town, city, region ('south east') or country. The allowed values will already exist on the database.

If a user is entered in a particular 'city' then it is assumed they can travel within all 'towns' inside that city. Likewise if region was selected, they will be found under all cities, hence, all towns. They would only be listed under 1 country however.

I would like to then be able to search this database by either of the above, so 'town' will search just the selected town within it's designated city/region/country. Whereas selecting region will search within all cities and all towns associated.

My current decision;

4 static data tables;
Town/City/Region/Country

1 link table
(fields) town/city/region/country

The link would then contain a reference to all of the static data to link the towns/cities/regions together. Instances where there are no sub places (a small city) would have a default value for the next.

The people added on the database would then have a single 'location' ID which refers to a link table which states field/ID. e.g. 'field=region', 'ID=6'

Could you please check this and let me know if my current design is sensible? I want this to be fast & efficient as opposed to just working, its tricky as I'm still learning.

Please advise,
Thank you very much for your time!

pete_bisby

What you could have is a single table with the following information, which works on a parent/child relationship with itself:
[INDENT]LocationID = auto_increment, primary key
LocationType = either [Country/Region/City/Town]
LocationName = Name of Location (e.g. England, Manchester, London, etc.)
LocationLink = Where this particular location is a "child" of[/INDENT]

As data is entered into the table, you will essentially be "drilling" through the Country, then Region, then City, then Town. All top-level (countries essentially) entries will have a LocationLink of zero. See the example below:
INDENT, (LocationType), (LocationName), (LocationLink)
1, Country, England, 0
2, Country, Scotland, 0
3, Country, Wales, 0
4, Region, North West England, 1
5, City, Manchester, 4
6, Town, Greater Manchester, 5[/INDENT]

So when a person selects a location (e.g. LocationId=6), you will be able to navigate upwards following the child/parent links - LocationId 6 links to 5, in turn links to 4, in turn links to 1.

Conversely, if a person selects a City you will immediately be able to see which Towns the person has access to. For example, person selects Manchester you would have "SELECT LocationName FROM tblLocation WHERE LocationLink=4".

kakki

no offense, pete_bisby,
the db will work that way...
but that forces you to do multiple queries on the table to get the full
"path" to the city/town/region ( whatever )
what about "group bys" ... like how many cities are in london ...
or ( even better ) show me the top 10 of "cities" in "towns"

@:
i think that will work better, because you can have ALL the data on one query
( and do "group-bys" )

pete_bisby

It's quite easy .... for example, London (entered as a City) has a LocationID of 10, and you want to find all the Towns within London:

SELECT A.LocationName, COUNT(B.LocationId)
FROM Locations A INNER JOIN Locations B ON A.LocationId=B.LocationLink
WHERE A.LocationId=10
GROUP BY A.LocationName;

You can't show top 10 Cities in Towns, because a Town belongs to (is the child of) a City, but you can do it easily the other way:

SELECT LocationName
FROM tblLocation
WHERE LocationLink=10
ORDER BY LocationName
LIMIT 0,10;

And if you want to find the path of any given location:

function findPath($intLocationId) {
  $aryPath[] = $intLocationId;
  while ($intLocation!=0) {
    $strQuery = "SELECT LocationLink FROM tblLocation WHERE LocationId=$intLocationId";
    $result = mysql_query($strQuery);
    $row = mysql_fetch_row($result);
    $intLocationId = $row[0];
  }

  return $aryPath;
}

This will return a path, starting at the lowest point (which could be City, but will work at any level), ending up at the Country where the LocationLink will always be zero. If you want the path starting at the highest point, either use array_pop() to add the LocationId to the end of the array, or use array_reverse().

This is probably the most efficient way - information contained in a single table, and you can still use GROUP BY SQL queries, if you wish. It may look a little strange at first, but we've been using this method for quite a few years now and it has never failed.