Sql-server – How to store World/event data in a treelike structure SQL/SQLite3

Use one of the various techniques for modeling hierarchical data in a relational database. My preferred approach is visitation numbers (see my answer to this question for more information about visitation numbers) but you could also use adjacency lists.

This will simplify the retrieval of Laws that pertain to a particular Location.

You will also want to include some kind of level indicator on your locations. This will allow local overrides of broader laws to be identified. You can sort by the level indicator to pick the most localalized version of any particular law.

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 })