CODEWITHSUNDEEP
database-design
user36670
user36670

Reputation: 63

Database Schema design

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.

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

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

Upvotes: 3

Views: 1843

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562260

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.

Upvotes: 1

Vyas Bharghava
Vyas Bharghava

Reputation: 6510

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?

Upvotes: 0

ChrisLively
ChrisLively

Reputation: 88044

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.

Upvotes: 4

Related Questions