SQL Server – Efficiently Creating Locality Entity with Country Entity

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.

Use a variation of number 2 - a location table with a involuted (self-referencing)relationship, but add something to make the hierarchy management easier. For example, use nested sets/visitation numbers (see here) or an adjacency list.

The advantages of this approach are that:

• You have a single foreign key from your transaction to your location table. This keeps your queries simple insofar as you don't need multiple joins and either/or logic.
• The foreign key can point to as vague or as specific a location as you need.
• You can easily traverse the hierarchy (since you'd be using a technique like nested sets) to get the level of geography of interest.
• You can accommodate countries that have more or less than one geographical division between the country and the city, without having to create dummy entries or duplicating data.

I don't know what your ORM is, but it should be able to handle a self-referencing relationship and a partitioning attribute (i.e. GeographyLevel = { country, state, city })