What is the best way to design a circuit for the following?
What is the best way to design a schema for the following requirement?
The need to store countries, states, counties and counties can be divided into regions. People may appear in regions with different data points to report.
Regions can also be divided into divisions, which are like groupings of people. So Region 1 could have Division A, Division B with people in each of these divisions. Regions and departments, each with a different set of metadata that differs from country, state, and county.
The reports will cover people-related data and google-like analytics with drilling from country all the way down to regions and divisions.
Note. Regions can have 10 people and 1 division with 4 people and the remaining 6 people are not tied to any division.
It looks like each person can have one and only one region.
If you are doing transactional processing (as opposed to data mining / warehousing) I would link this person to a ledger with a foreign key RegionID
.
As for the (optional) partition, you can associate a person with a division with reference: PersonID
, DivisionID
or if you do not mind NULL
DivisionID
, you can have a foreign key.
As far as the hierarchy for geographic regions is concerned, I would hesitate to model this until I know more about the constraints between countries and what these structures are. While it would be nice to think that everything always rises to the next level, I do know a lot about hierarchies where levels are skipped and they are modeled in a very different way. Also, many countries, such as the United Kingdom, would normally not have states (unless you intend to use England, Scotland, Wales and Northern Ireland). France is even more complex .
For the reporting / collapse aspect (or if you're only doing data mining / data warehouse), I would convert to a separate dimensional model that "locks" other things as attributes and makes collapsing easier to do. In this way, the star schema blocks the dimension identifiers for different levels.
source to share
Off the top of my head:
- countries, states, counties, cities have fk in regions.
- states have fk for countries
- counties have fk for states
- Cities have fk in County Counties
- have fk for divisions
- People have fk for divisions_people
- divisions_people has fk for people and divisions
-
divisions have fk for divisions_people
country <- state <- county <- city ^ ^ ^ ^ \ \ / / regions ^ | divisions ^ | \|/ divisions_people (1 person in multiple divisions) ^ | people
source to share
Country table: country_id, country_name, population
State table: state_id, state_name, country_id, population
County table: county_id, county_name, state_id, population
Table for regions: region_id, region_name, county_id, population
Table for partitions: division_id, division_name, region_id, population
Confirm in your code or through an associated constraint (depending on your RDBMS) that you do not have a 300-person unit in a 50-person region. To have people in the region without being in a division, your population in the region will be 500, and the sum of its divisions will be only 450 (leaving you 50 people in the region, but without division).
source to share