How to relate Agencies at the State, County, and Local Government levels

I’m having a bit of trouble deciding how to properly relate government agencies to their respective state, county, and local government levels.

I would like to be able to query for agencies that are specific to the level requested. For example, user requests all state level agencies for the state of Florida.

I would also like to be able to query for all agencies for any combination of levels requested. For example, user requests all county level agencies specifically for Orange County AND all local level agencies (cities, towns, villages, etc) found in Orange County.

That being said, looking at my database diagram that I have so far (attached below), would “junction” tables be the way to go to properly reference agencies to their respective governments to make those queries possible?

Current database diagram:

Any help or pointers in the right direction will be greatly appreciated!

Responses to comments:

Be cautious about assuming that the flow will always be Locality → County → State. There are cases of cities (localities in your diagram) that are not contained in any county. See https://en.wikipedia.org/wiki/Independent_city_(United_States). So you’ll want to include a mechanism for having a locality belong directly to a state.

Interesting, thanks for sharing. Perhaps the easiest way to account for those scenarios of independent cities would be to allow a NULL county_id in the FK of the localities table? What do you think?

If you use a NULL county_id, how will you connect an independent city to it’s state? Could you add a state_id FK to the localities table, and allow one or the other of it and county_id to be NULL, but not both?

I should be able to. It had crossed my mind to make state_id a FK in localities along with county_id, but because I wasn’t aware of the existence of independent cities in the U.S., I decided not to include it at the time. I will add that additional FK to the design, and only allow for the county_id to be NULL.

What are those id columns for?

I made the id column in each table serve as both a PK and identity that self-increments to allow me to relate each table. I will look into using the codes provided by the ISO standard.

Is this database meant to contain data about a one or more countries?

Only focused on U.S. currently.

Is there anything wrong with having one table for agencies and a column for the agency name and three columns for governments. State, county and locality. Assuming this is a national database, the government tables look OK.

This way if you wanted every county agency in Florida you would select where state is Florida and county_id is not null and locality_id is null or something like that. Or use blanks instead of NULL.