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

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?

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.

StackExchange Database Administrators Q#14940, answer score: 2