Sfrgttk

Why Third 'Reich'? Why is 'reich' not translated when 'third' is? What is the English synonym of reich?

Is it possible to detect 100% of SQLi with a simple regex?

Does a star need to be inside a galaxy?

How to not forget my phone in the bathroom?

Does the phrase がんばする makes sense?

Why does RAM (any type) access time decrease so slowly?

Which was the first story to feature helmets which reads your mind to control a machine?

How should I ship cards?

What have we got?

Why does the current not skip resistors R3 and R5 when R6 and R4 have no resistance?

Apparently I’m calling random numbers but nothing in call log?

Someone wants me to use my credit card at a card-only gas/petrol pump in return for cash

Suggestions on how to improve logo

How to explain one side of Super Earth is smoother than the other side?

Does limiting the number of sources help simplify the game for a new DM with new and experienced players?

Can a planet be tidally unlocked?

How to assess the susceptibility of a U.S. company to go bankrupt?

Father gets chickenpox, but doesn't infect his two children. How is this possible?

Simple Question About Conservation of Angular Momentum

Why do climate experts from the UN/IPCC rarely mention Grand Solar Minimum?

What happens when the last remaining players refuse to kill each other?

Would life expectancy increase if we replaced healthy organs with artificial ones?

Complicated square root problem.

Why there is no EEPROM in STM32F4 MCUs

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

Hierarchy for dimensional modelHelp with relational database designSSMS: Limit a login to be able to view and work only with some databases, but not able to make any changes on the server levelAre junction tables a good practice?Which better follows the design principles of relational databasesDesigning a globally distributed databaseAddress Parts - Hierarchy vs FlatCorrect Model for Users in a Collection with Items in that CollectionDatabase Design - multiple tablesWhich columns should be indexed when all may be used in different search queries?

0

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

share|improve this question

edited 26 secs ago

MDCCL

6,75731745

asked 4 hours ago

lmoraguezlmoraguez

11

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
  8 mins ago
  

  
  
  
  

add a comment | 

0

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

share|improve this question

edited 26 secs ago

MDCCL

6,75731745

asked 4 hours ago

lmoraguezlmoraguez

11

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

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

share|improve this question

edited 26 secs ago

MDCCL

6,75731745

asked 4 hours ago

lmoraguezlmoraguez

11

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

edited 26 secs ago

MDCCL

6,75731745

asked 4 hours ago

lmoraguezlmoraguez

11

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

edited 26 secs ago

MDCCL

6,75731745

asked 4 hours ago

lmoraguezlmoraguez

11

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 26 secs ago

MDCCL

6,75731745

edited 26 secs ago

MDCCL

6,75731745

asked 4 hours ago

lmoraguezlmoraguez

11

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

11

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

answered 3 hours ago

Randolph WestRandolph West

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
  

  
  
  
  

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.

draft saved

draft discarded

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

Name

Email

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-level%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

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

answered 3 hours ago

Randolph WestRandolph West

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
  

  
  
  
  

add a comment | 

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

answered 3 hours ago

Randolph WestRandolph West

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
  

  
  
  
  

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;

share|improve this answer

answered 3 hours ago

Randolph WestRandolph West

2,638215

SELECT name, type, latitude, longitude, zoom_lvl

FROM localities

WHERE county_id = 2;

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';

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

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