What is the best way of storing a geographical information in a relation db?

I want to save a geographical data in a relational db and be able to query for data based on their location (country, state or similar not coordinates).

My current solution is to have 4 extra fields (all countries I'm interested in have 2 or 3 administrative divisions) in my table and filter on strings. But I realize that this is a bad solution and would like to normalize my table.

I will also use that data to determine which page my users wants to visit, so it must be simple to lookup a request like "/usa/california/san_fransisco/..."

The only other solution I can come up with is to store those 4 extra fields in another table and link them with a foreign key but that wou开发者_Python百科ld still mean some data duplication as country name would duplicated in allot of rows.

Is there any better way of doing this?

---

Normalizing is definitely the way to go. Databases are designed to function that way. Yes the query might look long but it's not that bad. It might look something like this:

select * --or whatever fields you need
  from Customer
       left outer join City    on (Customers.CityID = City.CityID)
       left outer join State   on (City.StateID     = State.StateID)
       left outer join Country on (State.CountryID  = Country.CountryID)
 where CustomerID = 1234

---

You're on the right track with putting the info in tables. Their called lookup tables. If you want to go the full relational route, you can have the entity link with a foreign key to the city lookup table. The city table links to the state table. The state table links to the country table. You could also store a text version of the complete location in the entity's original table for data display.

---

My current solution is to have 4 extra fields (all countries I'm interested in have 2 or 3 administrative divisions) in my table and filter on strings. But I realize that this is a bad solution and would like to normalize my table.

I don't think that this is a bad solution. Storing simple geographical/address-based information per row and using WHERE to fetch all records that match is fairly standard procedure. Using a foreign key to link to a separate table is going to be additional work and won't be any faster.

The searching/request using a RESTful interface (as you suggested) is a good idea, however.

---

Go the normalized route. Joining tables is NOT slow, or complicated. PK of each table will be an integer with a clustered index. Foreign keys will have an index. The join is going to fly.

If you want to list cities in a drop down list, you don't want duplicates. You may list all the cities under a state. De-normalized will slow your query with "distinct", i guarantee you that is slower going the de-normalized route. ironic?

But there is a case for de-normalized. There are millions of addresses. It will probably not be feasible to enter all addresses in your application. So you are going to rely on..... free text input from the user. In this case you don't care about exact correctness or duplicates, you are forced to just accept whatever is data is thrown at you due to the impossibility of having exhaustive data to validate against. And you would rather not bother inserting to "lookup" tables as you don't trust the input to begin with.

You could go for a re-cursive model if you want ultra flexibility to handle different countries. Some countries may not have states, counties, etc. They all have their own hierarchy.