Relate Agencies at the State, County, and Local Government levelsMirroring after restoring the database on a...
Why does finding small effects in large studies indicate publication bias?
What prevents people from lying about where they live in order to reduce state income taxes?
Does a star need to be inside a galaxy?
Bitcoin automatically diverted to bech32 address
Why do climate experts from the UN/IPCC rarely mention Grand Solar Minimum?
How bad is a Computer Science course that doesn't teach Design Patterns?
Why does RAM (any type) access time decrease so slowly?
Can a rabbi conduct a marriage if the bride is already pregnant from the groom?
Does the phrase がんばする makes sense?
Is there a technology capable of disabling the whole of Earth's satellitle network?
Copy the content of an environment
Would life expectancy increase if we replaced healthy organs with artificial ones?
Is the "hot network questions" element on Stack Overflow a dark pattern?
Pictures from Mars
Identical projects by students at two different colleges: still plagiarism?
A dragon's soul trapped in a ring of mind shielding wants a new body; what magic could enable her to do so?
Why is it a problem for Freddie if the guys from Munich did what he wanted?
Does an increasing sequence of reals converge if the difference of consecutive terms approaches zero?
Ethernet cable only works in certain positions
How to draw these kind of adjacent ovals with arrows in latex?
Why does calling Python's 'magic method' not do type conversion like it would for the corresponding operator?
Smooth Density Histogram with probability areas
Why are energy weapons seen as more acceptable in children's shows than guns that fire bullets?
How to use the viewer node?
Relate Agencies at the State, County, and Local Government levels
Mirroring after restoring the database on a new machineSSRS appears to be ignoring Permissions set using Report ManagerSQL Server 2014 .bak file error - The media family on device 'D:..' is incorrectly formedDatabase role to allow .NET program to retrieve MSDB table and view rowsBehavior of case sensitivity of RAISERROR when run remotelyT-SQL OPENROWSET Permission IssueHashtags and synonyms table designLogin lacks connect endpoint permission in SQL Server - Error 18456, State: 149Designing a globally distributed databaseWhich columns should be indexed when all may be used in different search queries?
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.
Through research, I've seen some examples where a clustered key was used, but I'm not entirely sure if that'll be helpful to me, as I haven't had any experience with those before.
Any help or pointers in the right direction will be greatly appreciated!
I've attached an image of what I've designed so far.
sql-server database-design relational-theory
asked 1 hour ago
lmoraguezlmoraguez
1
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.
Through research, I've seen some examples where a clustered key was used, but I'm not entirely sure if that'll be helpful to me, as I haven't had any experience with those before.
Any help or pointers in the right direction will be greatly appreciated!
I've attached an image of what I've designed so far.
sql-server database-design relational-theory
asked 1 hour ago
lmoraguezlmoraguez
1
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
45 mins 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.
Through research, I've seen some examples where a clustered key was used, but I'm not entirely sure if that'll be helpful to me, as I haven't had any experience with those before.
Any help or pointers in the right direction will be greatly appreciated!
I've attached an image of what I've designed so far.
sql-server database-design relational-theory
asked 1 hour ago
lmoraguezlmoraguez
1
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.
Through research, I've seen some examples where a clustered key was used, but I'm not entirely sure if that'll be helpful to me, as I haven't had any experience with those before.
Any help or pointers in the right direction will be greatly appreciated!
I've attached an image of what I've designed so far.
sql-server database-design relational-theory
sql-server database-design relational-theory
asked 1 hour ago
lmoraguezlmoraguez
1
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 1 hour ago
lmoraguezlmoraguez
1
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 1 hour ago
lmoraguezlmoraguez
1
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 1 hour ago
lmoraguezlmoraguez
1
asked 1 hour ago
lmoraguezlmoraguez
1
1
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
45 mins 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
45 mins 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
45 mins 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
45 mins 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 1 hour ago
Randolph WestRandolph West
2,638215
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%2frelate-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 1 hour ago
Randolph WestRandolph West
2,638215
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 1 hour ago
Randolph WestRandolph West
2,638215
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 1 hour 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 1 hour ago
Randolph WestRandolph West
2,638215
answered 1 hour ago
Randolph WestRandolph West
2,638215
answered 1 hour ago
Randolph WestRandolph West
2,638215
answered 1 hour ago
Randolph WestRandolph West
2,638215
2,638215
add a comment |
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%2frelate-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
45 mins ago