CODEWITHSUNDEEP
sqldatabase-design
Alex
Alex

Reputation: 59

Question on DB design: how to reference to different type of geo regions

I’m currently developing BD for macroeconomic statistics, which will include time series for GDP, inflation, population and others for different countries and regions. Could you please advise the best way how to design the schema for it? I’m currently struggling how to make references to the geographic regions. There are different types of geo regions – macro regions (EU, EMEA, EM and etc, consisting of several countries), single countries and micro regions (like US states). There could be other types of regions in the future. Most economic indicator will be attributed to single country, but some could be linked either to macro region or micro region. What is the best way to implement this in DB? Is it better to create separate tables for each type of region (countries, macro and micro regions), or it should be single tables with different types? And what is the best way to make the reference from table with indicators (tIndicator_values) to the table with counties/regions? I would be also very grateful if you could advise some resources/examples of the implementation of DB with macroeconomic statistics.

Upvotes: 0

Views: 79

Answers (1)

user330315
user330315

Reputation:

I would go with a single table region that identifies the type of the region and has a foreign key back to its "parent region"

create table region_type
(
  id integer primary key, 
  name varchar(20) not null unique
);

create table region 
(
   id integer primary key,
   name varchar(100) not null,
   type_id integer not null references region_type,
   parent_region_id integer references region
);

Then you would have rows like this: 

insert into region_type 
  (id, name)
values
  (1, 'political union'),
  (2, 'geographical'),
  (3, 'country'), 
  (4, 'state');

insert into region 
  (id, name, type, parent_region_id)
values
  (1, 'EMEA', 2, null),
  (2, 'EU', 1, 1),
  (3, 'Germany', 3, 1),
  (4, 'Bavaria', 4, 3);

Your indicator table would then reference the region table through a foreign key.

The hierarchy in the region table allows you to do aggregates on the different levels if needed, even if the indicators are only linked to e.g. the country.

Upvotes: 2

Related Questions