Database Schema design

Answer 1

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.

Answer 2

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?

Answer 3

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.