Database Schema design

Hi,

I am trying to design a location lookup in which the user can specify a location to any desired level of accuracy. eg. one of Country, State, City, Borough etc,

I have a used a common location table, which will then be used in a lookup with the table name selected dynamically, but was wondering if there is a feasible alternative way to do this.

Edit The hierarchical table looks like the way to go. Thanks for the tip.

From stackoverflow

• You might consider something like:

  locations
    id int
    parentId int
    name varchar(45)
  

  From this perspective you could load any type of location with any level depth.

  metanaito : You wouldn't be able to query a group of locations (for example, find all cities) with this structure. Maybe you could also add "type".

  le dorfier : This matches the element scope of the user story ("Any Location Description") with the data storage. As long as the user isn't specifying what format they are requiring (City, State, City-and-State) there's not much benefit from breaking it up in the database - just makes it more complex.

  le dorfier : You could add "IsState" boolean, and "State" column, then self-join from the lower-resolution names. You might want to anticipate not just cities, but parks, counties, other geographical entities.

• I see some problems with this design as (as I do not know your requirements fully): 1. You wouldn't be able to use simple joins. 2. Queries cannot be optimized as there cannot be indexes

  Alternate# Have you considered having a locationid in location table and having a link (one-to-many) to State & City tables?

  Walter Mitty : I think the column "Id" in the location table is actually a location ID, and the foreign keys that reference that link are already in the other two tables. Calling the first column of every table "ID" unfortunately undermines the mnemonic value of using names in the first place.

• Your design is better than Polymorphic Associations, which is an alternative that many people implement. It's kind of the reverse of what you've designed:

  CREATE TABLE location (
    id         INT PRIMARY KEY,
    table_name VARCHAR(45) NOT NULL CHECK (table_name in ('city', 'state')),
    table_id   INT NOT NULL -- matches an id in either city or state
  );
  

  Regarding your design, one potential weakness is that you have no way of enforcing that location.table accurately indicates the table that contains a reference to the location. So you might as well drop that column, and rely instead on joining to both tables.

  For example, to generate labels for a pick-list:

  SELECT l.id, COALESCE(s.name, c.name, 'Unknown Location') AS name
  FROM location AS l
    LEFT OUTER JOIN state AS s ON (l.id = s.location_id)
    LEFT OUTER JOIN city AS c ON (l.id = c.location_id);
  

  Chris Lively started to suggest using a hierarchical table. I think he means that you'd store both states and cities in the location table, and then you don't need the city and state tables at all.

  CREATE TABLE location (
    id         INT PRIMARY KEY,
    loc_type   VARCHAR(45) NOT NULL CHECK (table_name in ('city', 'state')),
    loc_name   VARCHAR(45) NOT NULL,
    parent_id  INT, -- NULL if root of tree
    FOREIGN KEY (parent_id) REFERENCES location(id)
  );
  

  There are other ways of storing a hierarchy in SQL too, but if you only ever have one level of depth, this design is adequate.