Database design

This page gathers the database design work for every ticket: data analysis, conceptual, logical and physical data models, data dictionary and decisions.

---

Table of contents

• Shared conventions
• Import countries
  • Data analysis
  • Conceptual data model (MCD)
  • Logical data model (MLD-R)
  • Physical data model (MPD)
  • Data dictionary

---

Shared conventions

These rules apply to every ticket.

Topic	Convention
DBMS	PostgreSQL 18, database created with SQL scripts versioned in the repository
Modelling method	MERISE: MCD -> MLD-R -> MPD, designed with Looping
MCD naming	French business vocabulary; entities in uppercase and singular; properties in lowercase; relationships as verbs in the infinitive
MLD-R / MPD naming	English, lowercase, words separated by _, tables in plural with the t_ prefix
Proof	Every size, type and constraint is justified by a measured value (SQL query) or a business rule
Secrets	No API key or password in the wiki, in scripts or in screenshots

---

Import countries

Ticket: link to the ticket · Author: Frank-readresolve · Last update: 2 October 2026

Data analysis

Before designing the model, the 254 countries returned by the REST Countries API were loaded into a raw staging table in PostgreSQL, then analysed with SQL queries.

Results (254 countries, 13 October 2026)

#	Question	Result	Design decision
Q1	Missing values per column	4 countries without ISO code nor flag (Abkhazia, Northern Cyprus, Somaliland, South Ossetia); 5 without subregion (Antarctica, Bouvet Island, French Southern and Antarctic Lands, Heard Island and McDonald Islands, South Georgia); no other missing value	NOT NULL on every column
Q2	Duplicate ISO codes	None among the countries that have a code	ISO alpha-2 code as identifier
Q2b	Duplicate country names	None	UNIQUE on the country name
Q3	Distinct regions and subregions	6 regions, 24 subregions for 254 countries	Many repetitions for few values -> separate entities
Q4	Maximum text lengths	Name: 44 - code: 2 - flag URL: 45 - region: 9 - subregion: 25	Column sizes (measured value + margin)
Q5	Smallest area	0.44 km² (Vatican City); 3 areas have decimals	Decimal type for the area
Q6	Does a subregion always give the same region?	Yes, no conflict on the 254 countries	Functional dependency subregion -> region
Q7	Countries kept	245 kept, 9 excluded	Expected number of rows in t_countries
Q8	Population range	Max: 1,429,404,000 (India)	Fits in a 32-bit signed integer (max 2,147,483,647)
Q9	Zero or negative numbers	2 populations equal to 0 (Bouvet Island, Heard Island and McDonald Islands), already excluded by Q1	CHECK constraints
Q10	Countries kept per region	Africa 59 - Americas 56 - Asia 50 - Europe 53 - Oceania 27 - Antarctic 0	Sum matches Q7; the Antarctic region would be empty

Conceptual data model (MCD)

Entities

Entity	Identifier	Properties
PAYS	code iso deux lettres	nom commun du pays, lien du drapeau du pays, superficie, population
SOUS-REGION	sous-région	/
REGION	région	/

nom commun du pays is shown in bold but not underlined: it is a second discriminant (candidate key). The ISO code was retained as identifier. It's defined by the international standard ISO 3166-1 alpha-2 which makes it stable over time, where a country name can change. The country name remains a candidate key: it is unique (checked by a query on the 254 countries and guaranteed by the business rule), which is enforced by the UNIQUE NOT NULL constraint.

Relationships and cardinalities

Relationship	Cardinalities	Reading	Evidence
PAYS - se situer - SOUS-REGION	1,1 / 1,n	A country is located in exactly one subregion; a subregion groups one or more countries	Q1 + Q7: every kept country has a subregion
SOUS-REGION - appartenir - REGION	1,1 / 1,n	A subregion belongs to exactly one region; a region contains one or more subregions	Q6 and Q3

Design decisions

Decision	Justification
Three entities instead of one	The region depends on the subregion, not directly on the country (Q6). Keeping it in the country would repeat the same value on many rows and allow inconsistencies (typos, partial updates).
ISO alpha-2 code as identifier of PAYS	It is defined by the international standard ISO 3166-1 alpha-2, so it is stable over time, whereas a country name can change.
Country name as candidate key	It is unique (checked by query on the 254 countries and guaranteed by the business rule).
Name as identifier of SOUS-REGION and REGION	Each of the 24 subregion names corresponds to only one subregion (Q3, Q6), so the name is enough to identify it.
No relative identifier / no composite key for SOUS-REGION	A subregion depends on its region (1,1 cardinality) but does not need the region to be identified. Relative identification is only needed when the same name can exist under several parents.
No Antarctic region	All its countries are excluded (Q10). It would be a region with no subregion, which contradicts the 1,n cardinality. Confirmed by the Product Owner.
No inter-relationship constraint	There is only one path between PAYS and REGION, so there is nothing to constrain between two relationships.

Logical data model (MLD-R)

Table	Primary key	Foreign key	Other columns
t_regions	region	/	/
t_subregions	subregion	region -> t_regions	/
t_countries	iso_code_alpha_2	subregion -> t_subregions	country_name (candidate key), flag_url, area_kilometers, population

Transformation rules applied: entities -> tables, properties -> columns, identifiers -> primary keys, 1,1 relationships -> foreign keys on the 1,1 side.

Normal forms

Form	Check	Result
1NF	Every attribute is atomic in this business context	All tables
2NF	Every primary key is a single column, so no partial dependency is possible	All tables
3NF	t_regions: no non-key attribute. t_subregions: one non-key attribute (region). t_countries: no non-key attribute depends on another non-key attribute; country_name is a candidate key	All tables

The region was moved out of the countries table precisely to remove the transitive dependency country -> subregion -> region.

Generated physical data model (MPD)

CREATE TABLE t_regions(
   region VARCHAR(10),
   PRIMARY KEY(region)
);
 
CREATE TABLE t_subregions(
   subregion VARCHAR(30),
   region VARCHAR(10) NOT NULL,
   PRIMARY KEY(subregion),
   FOREIGN KEY(region) REFERENCES t_regions(region)
);
 
CREATE TABLE t_countries(
   iso_code_alpha_2 CHAR(2),
   country_name VARCHAR(50) NOT NULL,
   flag_url VARCHAR(45) NOT NULL,
   area_kilometers DECIMAL(10,2) NOT NULL CHECK (area_kilometers > 0),
   population INT NOT NULL CHECK (population > 0),
   subregion VARCHAR(30) NOT NULL,
   PRIMARY KEY(iso_code_alpha_2),
   UNIQUE(country_name),
   FOREIGN KEY(subregion) REFERENCES t_subregions(subregion)
);

Integrity: the constraints (NOT NULL, UNIQUE, CHECK, foreign keys) are the last safeguard of data integrity whatever inserts the data.

Data dictionary

Table	Column	API field	Type	Constraints	Description	Justification
t_regions	region	region	VARCHAR(10)	PK	Geographic region (e.g. Europe, Americas)	Max measured: 9 characters
t_subregions	subregion	subregion	VARCHAR(30)	PK	Geographic subregion (e.g. Northern Europe)	Max measured: 25 characters (Australia and New Zealand)
t_subregions	region	/	VARCHAR(10)	FK, NOT NULL	Region the subregion belongs to	1,1 cardinality
t_countries	iso_code_alpha_2	codes.alpha_2	CHAR(2)	PK	ISO 3166-1 alpha-2 code	Fixed length guaranteed by the standard
t_countries	country_name	names.common	VARCHAR(50)	NOT NULL, UNIQUE	Common name in English	Max measured: 44 characters (Saint Helena, Ascension and Tristan da Cunha); no duplicate
t_countries	flag_url	flag.url_svg	VARCHAR(45)	NOT NULL	URL of the SVG flag	All 254 URLs measured at 45 characters. Observation, not a guarantee: if the provider changes its format, the insertion fails and the problem is detected immediately
t_countries	area_kilometers	area.kilometers	DECIMAL(10,2)	NOT NULL, CHECK > 0	Area in km²	Max: 17,098,242 (Russia, 8 digits before the decimal point); min: 0.44 (Vatican City); a country always has a positive area
t_countries	population	population	INT	NOT NULL, CHECK > 0	Number of inhabitants	Max: 1,429,404,000 (India), within the INT limit of 2,147,483,647; a population of 0 is inconsistent (PO decision)
t_countries	subregion	subregion	VARCHAR(30)	FK, NOT NULL	Subregion the country is located in	1,1 cardinality

---