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












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:



Current Database Design



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










share|improve this question









New contributor




lmoraguez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





















  • 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 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.

    – Doug Deden
    3 hours ago











  • @DougDeden 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?

    – lmoraguez
    1 hour ago











  • 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?

    – Doug Deden
    18 mins ago











  • About which country(ies) will the relevant database contain information? Do you have lists of the applicable State and/or County and/or Locality Codes (like, e.g., this list of USA state codes)?

    – MDCCL
    1 min ago


















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:



Current Database Design



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










share|improve this question









New contributor




lmoraguez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





















  • 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 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.

    – Doug Deden
    3 hours ago











  • @DougDeden 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?

    – lmoraguez
    1 hour ago











  • 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?

    – Doug Deden
    18 mins ago











  • About which country(ies) will the relevant database contain information? Do you have lists of the applicable State and/or County and/or Locality Codes (like, e.g., this list of USA state codes)?

    – MDCCL
    1 min ago
















1












1








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:



Current Database Design



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










share|improve this question









New contributor




lmoraguez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.












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:



Current Database Design



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







sql-server database-design






share|improve this question









New contributor




lmoraguez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




lmoraguez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 9 mins ago









MDCCL

6,75731745




6,75731745






New contributor




lmoraguez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked 4 hours ago









lmoraguezlmoraguez

62




62




New contributor




lmoraguez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





lmoraguez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






lmoraguez is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.













  • 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 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.

    – Doug Deden
    3 hours ago











  • @DougDeden 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?

    – lmoraguez
    1 hour ago











  • 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?

    – Doug Deden
    18 mins ago











  • About which country(ies) will the relevant database contain information? Do you have lists of the applicable State and/or County and/or Locality Codes (like, e.g., this list of USA state codes)?

    – MDCCL
    1 min ago





















  • 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 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.

    – Doug Deden
    3 hours ago











  • @DougDeden 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?

    – lmoraguez
    1 hour ago











  • 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?

    – Doug Deden
    18 mins ago











  • About which country(ies) will the relevant database contain information? Do you have lists of the applicable State and/or County and/or Locality Codes (like, e.g., this list of USA state codes)?

    – MDCCL
    1 min ago



















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 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.

– Doug Deden
3 hours ago





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 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.

– Doug Deden
3 hours ago













@DougDeden 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?

– lmoraguez
1 hour ago





@DougDeden 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?

– lmoraguez
1 hour ago













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?

– Doug Deden
18 mins ago





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?

– Doug Deden
18 mins ago













About which country(ies) will the relevant database contain information? Do you have lists of the applicable State and/or County and/or Locality Codes (like, e.g., this list of USA state codes)?

– MDCCL
1 min ago







About which country(ies) will the relevant database contain information? Do you have lists of the applicable State and/or County and/or Locality Codes (like, e.g., this list of USA state codes)?

– MDCCL
1 min ago












1 Answer
1






active

oldest

votes


















0














A clustered key is just a method to sort data at the storage layer, so isn't relevant to your question. Your diagram has foreign keys (FKs) from localities to counties to states, so that's half the battle won. What you're asking is how best to query this data, and that's a matter of table joins.



For the first example, assume you want to bring back all county level agencies for Orange County, of all types. Let's assume the county_id for OC is 2.



SELECT name, type, latitude, longitude, zoom_lvl
FROM localities
WHERE county_id = 2;


In the second example, assume you don't know the county_id, but you know the name:



SELECT loc.name, loc.type, loc.latitude, loc.longitude, loc.zoom_lvl
FROM localities AS loc
INNER JOIN counties AS cou
ON loc.county_id = cou.id
WHERE cou.name = 'Orange County';


If you want to filter on agency type, I'd recommend a type lookup table as well with its own FK, so that you can filter on that as well. In that case, you would have something like this (where type is an integer):



SELECT loc.name, loc.latitude, loc.longitude, loc.zoom_lvl
FROM localities AS loc
INNER JOIN counties AS cou
ON loc.county_id = cou.id
WHERE cou.name = 'Orange County'
AND loc.type = 3;





share|improve this answer
























  • Thank you for responding. You're correct, half the battle is won with the relations between localities, counties, and states tables, but I'm having a hard time building the dbo.agencies table (below the other 3 in my image) and its relations to the other 3 tables. My idea is to store every agency in the agencies table, regardless of what level of government it operates at, but how do I properly reference the other 3 tables... what columns and keys should I build out in the agencies table?

    – lmoraguez
    1 hour ago











  • I updated my question to clarify my question more.

    – lmoraguez
    1 hour ago











Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});






lmoraguez is a new contributor. Be nice, and check out our Code of Conduct.










draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230188%2fhow-to-relate-agencies-at-the-state-county-and-local-government-levels%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









0














A clustered key is just a method to sort data at the storage layer, so isn't relevant to your question. Your diagram has foreign keys (FKs) from localities to counties to states, so that's half the battle won. What you're asking is how best to query this data, and that's a matter of table joins.



For the first example, assume you want to bring back all county level agencies for Orange County, of all types. Let's assume the county_id for OC is 2.



SELECT name, type, latitude, longitude, zoom_lvl
FROM localities
WHERE county_id = 2;


In the second example, assume you don't know the county_id, but you know the name:



SELECT loc.name, loc.type, loc.latitude, loc.longitude, loc.zoom_lvl
FROM localities AS loc
INNER JOIN counties AS cou
ON loc.county_id = cou.id
WHERE cou.name = 'Orange County';


If you want to filter on agency type, I'd recommend a type lookup table as well with its own FK, so that you can filter on that as well. In that case, you would have something like this (where type is an integer):



SELECT loc.name, loc.latitude, loc.longitude, loc.zoom_lvl
FROM localities AS loc
INNER JOIN counties AS cou
ON loc.county_id = cou.id
WHERE cou.name = 'Orange County'
AND loc.type = 3;





share|improve this answer
























  • Thank you for responding. You're correct, half the battle is won with the relations between localities, counties, and states tables, but I'm having a hard time building the dbo.agencies table (below the other 3 in my image) and its relations to the other 3 tables. My idea is to store every agency in the agencies table, regardless of what level of government it operates at, but how do I properly reference the other 3 tables... what columns and keys should I build out in the agencies table?

    – lmoraguez
    1 hour ago











  • I updated my question to clarify my question more.

    – lmoraguez
    1 hour ago
















0














A clustered key is just a method to sort data at the storage layer, so isn't relevant to your question. Your diagram has foreign keys (FKs) from localities to counties to states, so that's half the battle won. What you're asking is how best to query this data, and that's a matter of table joins.



For the first example, assume you want to bring back all county level agencies for Orange County, of all types. Let's assume the county_id for OC is 2.



SELECT name, type, latitude, longitude, zoom_lvl
FROM localities
WHERE county_id = 2;


In the second example, assume you don't know the county_id, but you know the name:



SELECT loc.name, loc.type, loc.latitude, loc.longitude, loc.zoom_lvl
FROM localities AS loc
INNER JOIN counties AS cou
ON loc.county_id = cou.id
WHERE cou.name = 'Orange County';


If you want to filter on agency type, I'd recommend a type lookup table as well with its own FK, so that you can filter on that as well. In that case, you would have something like this (where type is an integer):



SELECT loc.name, loc.latitude, loc.longitude, loc.zoom_lvl
FROM localities AS loc
INNER JOIN counties AS cou
ON loc.county_id = cou.id
WHERE cou.name = 'Orange County'
AND loc.type = 3;





share|improve this answer
























  • Thank you for responding. You're correct, half the battle is won with the relations between localities, counties, and states tables, but I'm having a hard time building the dbo.agencies table (below the other 3 in my image) and its relations to the other 3 tables. My idea is to store every agency in the agencies table, regardless of what level of government it operates at, but how do I properly reference the other 3 tables... what columns and keys should I build out in the agencies table?

    – lmoraguez
    1 hour ago











  • I updated my question to clarify my question more.

    – lmoraguez
    1 hour ago














0












0








0







A clustered key is just a method to sort data at the storage layer, so isn't relevant to your question. Your diagram has foreign keys (FKs) from localities to counties to states, so that's half the battle won. What you're asking is how best to query this data, and that's a matter of table joins.



For the first example, assume you want to bring back all county level agencies for Orange County, of all types. Let's assume the county_id for OC is 2.



SELECT name, type, latitude, longitude, zoom_lvl
FROM localities
WHERE county_id = 2;


In the second example, assume you don't know the county_id, but you know the name:



SELECT loc.name, loc.type, loc.latitude, loc.longitude, loc.zoom_lvl
FROM localities AS loc
INNER JOIN counties AS cou
ON loc.county_id = cou.id
WHERE cou.name = 'Orange County';


If you want to filter on agency type, I'd recommend a type lookup table as well with its own FK, so that you can filter on that as well. In that case, you would have something like this (where type is an integer):



SELECT loc.name, loc.latitude, loc.longitude, loc.zoom_lvl
FROM localities AS loc
INNER JOIN counties AS cou
ON loc.county_id = cou.id
WHERE cou.name = 'Orange County'
AND loc.type = 3;





share|improve this answer













A clustered key is just a method to sort data at the storage layer, so isn't relevant to your question. Your diagram has foreign keys (FKs) from localities to counties to states, so that's half the battle won. What you're asking is how best to query this data, and that's a matter of table joins.



For the first example, assume you want to bring back all county level agencies for Orange County, of all types. Let's assume the county_id for OC is 2.



SELECT name, type, latitude, longitude, zoom_lvl
FROM localities
WHERE county_id = 2;


In the second example, assume you don't know the county_id, but you know the name:



SELECT loc.name, loc.type, loc.latitude, loc.longitude, loc.zoom_lvl
FROM localities AS loc
INNER JOIN counties AS cou
ON loc.county_id = cou.id
WHERE cou.name = 'Orange County';


If you want to filter on agency type, I'd recommend a type lookup table as well with its own FK, so that you can filter on that as well. In that case, you would have something like this (where type is an integer):



SELECT loc.name, loc.latitude, loc.longitude, loc.zoom_lvl
FROM localities AS loc
INNER JOIN counties AS cou
ON loc.county_id = cou.id
WHERE cou.name = 'Orange County'
AND loc.type = 3;






share|improve this answer












share|improve this answer



share|improve this answer










answered 3 hours ago









Randolph WestRandolph West

2,638215




2,638215













  • Thank you for responding. You're correct, half the battle is won with the relations between localities, counties, and states tables, but I'm having a hard time building the dbo.agencies table (below the other 3 in my image) and its relations to the other 3 tables. My idea is to store every agency in the agencies table, regardless of what level of government it operates at, but how do I properly reference the other 3 tables... what columns and keys should I build out in the agencies table?

    – lmoraguez
    1 hour ago











  • I updated my question to clarify my question more.

    – lmoraguez
    1 hour ago



















  • Thank you for responding. You're correct, half the battle is won with the relations between localities, counties, and states tables, but I'm having a hard time building the dbo.agencies table (below the other 3 in my image) and its relations to the other 3 tables. My idea is to store every agency in the agencies table, regardless of what level of government it operates at, but how do I properly reference the other 3 tables... what columns and keys should I build out in the agencies table?

    – lmoraguez
    1 hour ago











  • I updated my question to clarify my question more.

    – lmoraguez
    1 hour ago

















Thank you for responding. You're correct, half the battle is won with the relations between localities, counties, and states tables, but I'm having a hard time building the dbo.agencies table (below the other 3 in my image) and its relations to the other 3 tables. My idea is to store every agency in the agencies table, regardless of what level of government it operates at, but how do I properly reference the other 3 tables... what columns and keys should I build out in the agencies table?

– lmoraguez
1 hour ago





Thank you for responding. You're correct, half the battle is won with the relations between localities, counties, and states tables, but I'm having a hard time building the dbo.agencies table (below the other 3 in my image) and its relations to the other 3 tables. My idea is to store every agency in the agencies table, regardless of what level of government it operates at, but how do I properly reference the other 3 tables... what columns and keys should I build out in the agencies table?

– lmoraguez
1 hour ago













I updated my question to clarify my question more.

– lmoraguez
1 hour ago





I updated my question to clarify my question more.

– lmoraguez
1 hour ago










lmoraguez is a new contributor. Be nice, and check out our Code of Conduct.










draft saved

draft discarded


















lmoraguez is a new contributor. Be nice, and check out our Code of Conduct.













lmoraguez is a new contributor. Be nice, and check out our Code of Conduct.












lmoraguez is a new contributor. Be nice, and check out our Code of Conduct.
















Thanks for contributing an answer to Database Administrators Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230188%2fhow-to-relate-agencies-at-the-state-county-and-local-government-levels%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Liste der Baudenkmale in Friedland (Mecklenburg)

Single-Malt-Whisky

Czorneboh