Sfrgttk

How to draw these kind of adjacent ovals with arrows in latex?

Found a major flaw in paper from home university – to which I would like to return

Do error bars on probabilities have any meaning?

Are there any rules or guidelines about the order of saving throws?

Is a 1hr 3min layover at ORD (Chicago) enough when coming in international?

Buying a "Used" Router

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

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

Is layered encryption more secure than long passwords?

Should corporate security training be tailored based on a users' job role?

Why did Tywin never remarry?

Is it appropriate to give a culturally-traditional gift to a female coworker?

A dragon's soul trapped in a ring of mind shielding wants a new body; what magic could enable her to do so?

Workplace intimidation due to child's chronic health condition

Why is Bernie Sanders maximum accepted donation on actblue $5600?

I'm struggling to say 'struggle'

Why are energy weapons seen as more acceptable in children's shows than guns that fire bullets?

Run a command that requires sudo after a time has passed

Why does finding small effects in large studies indicate publication bias?

What happens if you declare more than $10,000 at the US border?

Is 'bad luck' with former employees a red flag?

Can a rabbi conduct a marriage if the bride is already pregnant from the groom?

Which was the first story to feature space elevators?

The idea behind the state of Imperative languages

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

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?

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:

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

sql-server database-design

share|improve this question

edited 12 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 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
  20 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
  3 mins ago
  
  
  

  
  
  
  

add a comment | 

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:

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

sql-server database-design

share|improve this question

edited 12 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 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
  20 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
  3 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 12 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.

share|improve this question

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

share|improve this question

edited 12 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 12 mins ago

MDCCL

6,75731745

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

asked 4 hours ago

lmoraguezlmoraguez

62

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-levels%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