What is the best way to store geographic information regarding db?

Question

I want to store geographic data in a relational database and be able to request data based on their location (country, state or similar non-coordinates).

My current solution is to have 4 additional fields (all countries in which I am interested have 2 or 3 administrative sections) in my table and filter the rows. But I understand that this is a bad decision and I would like to normalize the table.

I will also use this data to determine which page my users want to visit, so you just need to find a request, for example: "/ usa / california / san_fransisco / ..."

The only other solution I can come up with is to save these 4 additional fields in another table and associate them with a foreign key, but this still means duplicating the data, as the name of the country will be duplicated in the highlighted lines.

Is there a better way to do this?

Answer 1

Normalization is definitely the way to go. Databases are designed to work this way. Yes, the request may look long, but it’s not so 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

Answer 2

, . . , . . . .

Answer 3

, 4 ( , , 2 3 ) . , .

, . / WHERE , . .

/ RESTful ( ) - .

Answer 4

. . PK . . .

, . . - "", , - . ?

-. . , . , ..... . , , , - . , .

, , . , .. .