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!
sql-server database-design
edited 9 mins ago
MDCCL
6,75731745
asked 4 hours ago
lmoraguezlmoraguez
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.
add a comment |
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!
sql-server database-design
edited 9 mins ago
MDCCL
6,75731745
asked 4 hours ago
lmoraguezlmoraguez
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.
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 NULLcounty_idin the FK of thelocalitiestable? What do you think?
– lmoraguez
1 hour ago
If you use a NULLcounty_id, how will you connect an independent city to it's state. Could you add astate_idFK to the localities table, and allow one or the other of it andcounty_idto 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
add a comment |
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!
sql-server database-design
edited 9 mins ago
MDCCL
6,75731745
asked 4 hours ago
lmoraguezlmoraguez
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.
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!
sql-server database-design
sql-server database-design
edited 9 mins ago
MDCCL
6,75731745
asked 4 hours ago
lmoraguezlmoraguez
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.
edited 9 mins ago
MDCCL
6,75731745
asked 4 hours ago
lmoraguezlmoraguez
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.
edited 9 mins ago
MDCCL
6,75731745
edited 9 mins ago
MDCCL
6,75731745
edited 9 mins ago
MDCCL
6,75731745
6,75731745
asked 4 hours ago
lmoraguezlmoraguez
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.
asked 4 hours ago
lmoraguezlmoraguez
62
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 NULLcounty_idin the FK of thelocalitiestable? What do you think?
– lmoraguez
1 hour ago
If you use a NULLcounty_id, how will you connect an independent city to it's state. Could you add astate_idFK to the localities table, and allow one or the other of it andcounty_idto 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
add a comment |
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 NULLcounty_idin the FK of thelocalitiestable? What do you think?
– lmoraguez
1 hour ago
If you use a NULLcounty_id, how will you connect an independent city to it's state. Could you add astate_idFK to the localities table, and allow one or the other of it andcounty_idto 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
add a comment |
1 Answer
1
active
oldest
votes
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;
answered 3 hours ago
Randolph WestRandolph West
2,638215
Thank you for responding. You're correct, half the battle is won with the relations betweenlocalities,counties, andstatestables, but I'm having a hard time building thedbo.agenciestable (below the other 3 in my image) and its relations to the other 3 tables. My idea is to store every agency in theagenciestable, 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 theagenciestable?
– lmoraguez
1 hour ago
I updated my question to clarify my question more.
– lmoraguez
1 hour ago
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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;
answered 3 hours ago
Randolph WestRandolph West
2,638215
Thank you for responding. You're correct, half the battle is won with the relations betweenlocalities,counties, andstatestables, but I'm having a hard time building thedbo.agenciestable (below the other 3 in my image) and its relations to the other 3 tables. My idea is to store every agency in theagenciestable, 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 theagenciestable?
– lmoraguez
1 hour ago
I updated my question to clarify my question more.
– lmoraguez
1 hour ago
add a comment |
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;
answered 3 hours ago
Randolph WestRandolph West
2,638215
Thank you for responding. You're correct, half the battle is won with the relations betweenlocalities,counties, andstatestables, but I'm having a hard time building thedbo.agenciestable (below the other 3 in my image) and its relations to the other 3 tables. My idea is to store every agency in theagenciestable, 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 theagenciestable?
– lmoraguez
1 hour ago
I updated my question to clarify my question more.
– lmoraguez
1 hour ago
add a comment |
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;
answered 3 hours ago
Randolph WestRandolph West
2,638215
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;
answered 3 hours ago
Randolph WestRandolph West
2,638215
answered 3 hours ago
Randolph WestRandolph West
2,638215
answered 3 hours ago
Randolph WestRandolph West
2,638215
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 betweenlocalities,counties, andstatestables, but I'm having a hard time building thedbo.agenciestable (below the other 3 in my image) and its relations to the other 3 tables. My idea is to store every agency in theagenciestable, 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 theagenciestable?
– lmoraguez
1 hour ago
I updated my question to clarify my question more.
– lmoraguez
1 hour ago
add a comment |
Thank you for responding. You're correct, half the battle is won with the relations betweenlocalities,counties, andstatestables, but I'm having a hard time building thedbo.agenciestable (below the other 3 in my image) and its relations to the other 3 tables. My idea is to store every agency in theagenciestable, 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 theagenciestable?
– 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
add a comment |
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.
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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_idin the FK of thelocalitiestable? 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 astate_idFK to the localities table, and allow one or the other of it andcounty_idto 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