1

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:

enter image description here

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.

  • I deleted my original answer because I kind of missed the point of your question. So here’s a comment to say I’m a fan of cross-reference tables, or what you call junction tables, for this kind of requirement. It allows you to associate elements that may not follow the hierarchy of your original three tables. – Randolph West Feb 20 at 7:16
  • I'd be tempted to use one of the standard techniques for hierarchical data to represent all your location types. Depending on your SQL Server version you might have a built-in way available, or there's always Nested Sets. – AakashM Feb 21 at 15:04
1

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.

  • Alen, do you mean to reference the states counties and localities tables directly with the agencies table, then aside from the agency_id and agency_name columns, to add 3 columns for state_id, county_id, and locality_id, and then to apply specific unique constraints? – lmoraguez Feb 20 at 19:56
  • Yes.Have an agencies table with the agency name and columns for the state name, locality name and county name or their ID's and any other columns you may need. I think it would be easier to filter the data via one table then coding logic to figure out which table to look in for an agency. A lot of agencies will have the same or similar names across states and counties and towns, so having one table will make it easier to find the right one. – Alen Feb 21 at 14:03

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.