How do you structure a database that organizes post by location like craigslist?

Question

I'm new to databases and web development so I'm just looking for a laymen explanation. No need for specific code, just theory. I understand OneToMany and ManyToMany relationships, so no need to explain that. I'm just wondering how someone would handle posts that are categorized by location the same way craigslist does it, eg. Country > State > City > County.

Let's say you have a PostTable that holds all posts site-wide. Do you have 4 fields in this table dedicated to CountryID, StateID, CityID, CountyID? Or do you have 1 field that lists CountyID and from that ID determine what Country, State, and City it's a part of?

Answer 1

Create a table called states.

Create a table called cities that as a many to one relation ship for a table called states.

Then in your post table you have a many to one to the cities tables.

Then you can use syntax like post.city.state.name to get the name of the state for the post or just post.city.name for the city.

Answer 2

For a given table called PostTable

Here is the Country Table

CREATE TABLE Country
(
    id int not null,
    name varchar(32) not null,
    unique key (name),
    primary key (id)
);

OK, what about State / Province?

CREATE TABLE State
(
    id int not null,
    country_id int not null,
    name varchar(32) not null,
    primary key (id),
    unique key (name)
);

How about Counties?

CREATE TABLE County
(
    id int not null,
    country_id int not null,
    state_id int not null,
    name varchar(32) not null,
    unique key (name,state_id),
    primary key (id)
);

Why would the County have to have (name,state_id) as a unique key combo? For example, there are 10 countries in the USA named Orange County (One is in New York, another is in California)

Let's go with City

CREATE TABLE City
(
    id int not null,
    county_id int not null,
    state_id int not null,
    name varchar(32) not null,
    unique key (name,state_id),
    unique key (name,country_id),
    primary key (id)
);

Why would the City have to have (name,country_id) as a unique key combo? For example, there is City named Rome in all Continents.

Why would the City have to have (name,state_id) as a unique key combo? For example, there is Jersey City, NJ and Jersey City, WI. There is also Hollywood, CA and Hollywood, FL.

Based on this, you could do your JOINs from these perspectives

• Country -> State
• State -> County
• County -> City
• State -> City

Just remember that the UNIQUE KEY declarations allow combinations of

• identical County Name in multiple States
• identical City Name in multiple States
• identical City Name in multiple Countries

I am not in the mood for figuring out strange oddities like

• Minneapolis-StPaul, Minnesota
• Kansas City, MO and Kansas City, KS
• Any City that sits in two different Counties

To shorten JOINs, you could just keep country_id, county_id, state_id, city_id with each PostTable row. Otherwise, you could be chaining all these tables deeper and deeper. This would definitely require proper tuning of sort_buffer_size and join_buffer_size.

Answer 3

This is a simple option:

You have a States table, that contains an id and a name.
You have a Counties table, that contains an id, a name, and a state_id.
You have a Cities table, which contains an id, a name, and a county_id.
You have a Posts table, which contains an id, a title, a city_id, and your post_text, etc.

When someone is searching for a location, you present them with a list of states. They select a state, then are presented with a list of counties. The query basically says "give me all of the counties where the state is X." Let's stop here for limiting search results.

To get the data to show to the user, you get all of the posts with a city_id that exists within the county_id (which exists within the originally selected state_id). You said not to provide code, so I'll leave out the query for this, but you'll likely use joins to get your display data (such as the state_name).

This reduces the number of times you store certain data, such as the state_name. With this normalized model, you store it once, then refer to the id of that row instead of providing the data itself. State names will likely not change, but in most other situations, it helps to store data like this so that you only have to change the 'name' (as an example) once.