Database Schema Design

Question

I am trying to create a location search in which the user can point the location to any desired level of accuracy. eg. one of a country, state, city, city, etc.,

I have a shared location table that will then be used in a search with the table name selected dynamically, but wondered if there is a possible alternative way to do this.

alt text http://img386.imageshack.us/img386/3842/locationschemadh6.png

Change . The hierarchical table looks like a way. Thanks for the tip.

Answer 1

You might consider something like:

locations
  id int
  parentId int
  name varchar(45)

From this point of view, you can load any location with any level depth.

Answer 2

, , , . , :

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
);

, , , location.table , location. , .

, :

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);

. , , location, city state.

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)
);

SQL, , .

Answer 3

I see some problems with this design as (since I do not fully know your requirements): 1. You cannot use simple connections. 2. Queries cannot be optimized because indexes cannot be

Alternative # Do you consider the presence of a locationid in the location table and the presence of a one-to-many link to the State and City tables?