Database schema documentation

The complete schema looks like this:

Let’s have a look at each table and column.

datacenters

id

Type: INTEGER

This is the unique identifier to a Datacenter, it doesn’t mean anything outside of DCWatch database.

name

Type: VARCHAR(255) aka a string limited in length

This is the name of the Datacenter. It may contain the name of the company operating the datacenter as well as this helps sometimes to identify it without having to check for the companies table. Also, sometimes the datacenters have very short names that might collide from one company to another.

latitude

Type: DECIMAL

This is the latitude GPS coordinate.

longitude

Type: DECIMAL

This is the longitude GPS coordinates.

address

] Type: TEXT, aka a potentially very long string

This is the address to locate the datacenter, not including the city_name, city/postal code or any other information that has a dedicated column.

city_name

Type: VARCHAR(255)

The name of the city / town to locate the datacenter.

department

Type: INTEGER

The official number identifying the department to locate the datecenter. This has been defined based on data collected in France and may change to adapt to other countries in future releases.

city_code

Type: VARCHAR(255)

The code identifying the city/town in a country, to locate the datacenter. This is sometimes referred as the ‘postal code’.

region

Type: VARCHAR(255)

Name of the region/land to locate the datacenter.

country_id

Type: INTEGER References: countries(id)

The internal DCWatch id to identify the country to locate the datacenter.

tier_uptime_institute

Type: SMALLINT

The Uptime Institute certification level for resilience: 1 for Tier 1, 2 for Tier 2, 3 for Tier 3, 4 for Tier 4

campus

Type: INTEGER

The DCWatch internal id to identify a datacenter campus where this datacenter is located.

total_floor_area_sqm

Type: DECIMAL

The estimated total floor area inside the building, in square meters.

IT_floor_area_sqm

Type: DECIMAL

The estimated floor area, dedicated to IT rooms, inside the building, in square meters.

TODO

"cooling_technologies" INTEGER,
"power_total_mw" DECIMAL,
"operation_start_year" INTEGER,
"heat_recovery" BOOLEAN,
"electricity_generation" INTEGER,
"PUE" DECIMAL,
"WUE" DECIMAL,
"ERF" DECIMAL,
"REF" DECIMAL,
"CUE" DECIMAL,
"progress_step_id" INTEGER REFERENCES progress_steps(id)

Here is the schema described in sql:

CALL enable_logging(storage_path = './');

CREATE SEQUENCE id_seq_progress_steps START 1;
CREATE TABLE IF NOT EXISTS "progress_steps" (
    "id" INTEGER PRIMARY KEY DEFAULT nextval('id_seq_progress_steps') NOT NULL UNIQUE,
    "name" VARCHAR(255) NOT NULL,
);


CREATE SEQUENCE id_seq_countries START 1;
CREATE TABLE IF NOT EXISTS "countries" (
	"id" INTEGER PRIMARY KEY DEFAULT nextval('id_seq_countries') NOT NULL UNIQUE ,
	"name" VARCHAR(255)
);


CREATE SEQUENCE id_seq_datacenters START 1;
CREATE TABLE IF NOT EXISTS "datacenters" (
	"id" INTEGER PRIMARY KEY DEFAULT nextval('id_seq_datacenters') NOT NULL UNIQUE,
	"name" VARCHAR(255) NOT NULL UNIQUE,
	"latitude" DECIMAL,
	"longitude" DECIMAL,
	"address" TEXT,
	"city_name" VARCHAR(255),
	"department" INTEGER,
	"city_code" VARCHAR(255),
	"region" VARCHAR(255),
	"country_id" INTEGER REFERENCES countries(id),
	"tier_uptime_institute" SMALLINT,
	"campus" INTEGER,
	"total_power_per_total_floor_area_ratio" DECIMAL,
	"total_floor_area_sqm" DECIMAL,
	"IT_floor_area_sqm" DECIMAL,
	"cooling_technologies" INTEGER,
	"power_total_mw" DECIMAL,
	"operation_start_year" INTEGER,
	"heat_recovery" BOOLEAN,
	"electricity_generation" INTEGER,
	"PUE" DECIMAL,
	"WUE" DECIMAL,
	"ERF" DECIMAL,
	"REF" DECIMAL,
	"CUE" DECIMAL,
	"progress_step_id" INTEGER REFERENCES progress_steps(id)
);

CREATE SEQUENCE id_seq_land_plots START 1;
CREATE TABLE IF NOT EXISTS "land_plots" (
	"id" INTEGER PRIMARY KEY DEFAULT nextval('id_seq_land_plots') NOT NULL UNIQUE,
	"official_id" VARCHAR(255),
	"surface_sqm" DECIMAL NOT NULL,
	"country_id" INTEGER REFERENCES countries(id)
);

CREATE SEQUENCE id_seq_buildings START 1;
CREATE TABLE IF NOT EXISTS "buildings" (
	"id" INTEGER PRIMARY KEY DEFAULT nextval('id_seq_buildings') NOT NULL UNIQUE ,
	"area" DECIMAL,
	"height" DECIMAL,
	"total_floor_area_sqm" DECIMAL,
	"IT_floor_area_sqm" DECIMAL,
	"datacenter_id" INTEGER REFERENCES datacenters(id),
	"construction_start_date" DATE,
	"land_plot_id" INTEGER REFERENCES land_plots(id),
);

CREATE SEQUENCE id_seq_campuses START 1;
CREATE TABLE IF NOT EXISTS "campuses" (
	"id" INTEGER PRIMARY KEY DEFAULT nextval('id_seq_campuses')  NOT NULL UNIQUE ,
	"name" VARCHAR(255),
);

CREATE SEQUENCE id_seq_companies START 1;
CREATE TABLE IF NOT EXISTS "companies" (
	"id" INTEGER PRIMARY KEY DEFAULT nextval('id_seq_companies') NOT NULL UNIQUE ,
	"name" VARCHAR(255),
	"identification_number" BIGINT,
	"belongs_to" INTEGER
);

CREATE SEQUENCE id_seq_groups START 1;
CREATE TABLE IF NOT EXISTS "groups" (
	"id" INTEGER PRIMARY KEY DEFAULT nextval('id_seq_groups') NOT NULL UNIQUE ,
	"name" VARCHAR(255)
);

CREATE SEQUENCE id_seq_datacenter_categories START 1;
CREATE TABLE IF NOT EXISTS "datacenter_categories" (
	"id" INTEGER PRIMARY KEY DEFAULT nextval('id_seq_datacenter_categories') NOT NULL UNIQUE ,
	"name" VARCHAR(255),
	"max_sdp_surface" INTEGER,
	"min_sdp_surface" INTEGER,
	"max_total_power" INTEGER,
	"min_total_power" INTEGER,
	"estimated_power_total_mw_to_total_floor_area_sqm" DECIMAL
);

CREATE SEQUENCE id_seq_cooling_technologies START 1;
CREATE TABLE IF NOT EXISTS "cooling_technologies" (
	"id" INTEGER PRIMARY KEY DEFAULT nextval('id_seq_cooling_technologies') NOT NULL UNIQUE ,
	"name" VARCHAR(255),
);

CREATE SEQUENCE id_seq_estimations START 1;
CREATE TABLE IF NOT EXISTS "estimations" (
	"id" INTEGER PRIMARY KEY DEFAULT nextval('id_seq_estimations') NOT NULL UNIQUE ,
	"name" VARCHAR(255),
	"id_datacenter" INTEGER,
	"value" DECIMAL,
	"unit" VARCHAR(255),
	"timestamp" DATE
);

CREATE SEQUENCE id_seq_electricity_generation_source START 1;
CREATE TABLE IF NOT EXISTS "electricity_generation_source" (
	"id" INTEGER PRIMARY KEY DEFAULT nextval('id_seq_electricity_generation_source') NOT NULL UNIQUE ,
	"name" VARCHAR(255)
);

CREATE SEQUENCE id_seq_log_table START 1;
CREATE TABLE IF NOT EXISTS "log_table" (
	"id" INTEGER PRIMARY KEY DEFAULT nextval('id_seq_log_table') NOT NULL UNIQUE ,
	"timestamp" DATE,
	"table" VARCHAR(255),
	"column" VARCHAR(255),
	"entry_id" INTEGER,
	"value" VARCHAR(255),
	"description" TEXT
);

-- ALTER TABLE "datacenters"
-- ADD FOREIGN KEY("operator_id") REFERENCES "companies"("id")
-- ON UPDATE NO ACTION ON DELETE NO ACTION;
CREATE SEQUENCE id_seq_company_operates_datacenter START 1;
CREATE TABLE IF NOT EXISTS company_operates_datacenter(
    id INTEGER PRIMARY KEY DEFAULT nextval('id_seq_company_operates_datacenter'),
    datacenter_id INTEGER REFERENCES datacenters(id),
    company_id INTEGER REFERENCES companies(id),
    date DATE
);
-- ALTER TABLE "datacenters"
-- ADD FOREIGN KEY("category") REFERENCES "datacenter_categories"("id")
-- ON UPDATE NO ACTION ON DELETE NO ACTION;
CREATE SEQUENCE id_seq_datacenter_is_in_categories START 1;
CREATE TABLE IF NOT EXISTS datacenter_is_in_categories (
    id INTEGER PRIMARY KEY DEFAULT nextval('id_seq_datacenter_is_in_categories'),
    datacenter_id INTEGER REFERENCES datacenters(id),
    category_id INTEGER REFERENCES datacenter_categories(id),
);
-- ALTER TABLE "buildings"
-- ADD FOREIGN KEY("datacenter_id") REFERENCES "datacenters"("id")
-- ON UPDATE NO ACTION ON DELETE NO ACTION;
CREATE SEQUENCE id_seq_datacenter_is_composed_of_buildings START 1;
CREATE TABLE IF NOT EXISTS datacenter_is_composed_of_buildings (
    id INTEGER PRIMARY KEY DEFAULT nextval('id_seq_datacenter_is_composed_of_buildings'),
    datacenter_id INTEGER REFERENCES datacenters(id),
    building_id INTEGER REFERENCES buildings(id),
);
-- ALTER TABLE "datacenters"
-- ADD FOREIGN KEY("cooling_technologies") REFERENCES "datacenter_to_categories"("datacenter_id")
-- ON UPDATE NO ACTION ON DELETE NO ACTION;
CREATE SEQUENCE id_seq_datacenter_uses_cooling_technologies START 1;
CREATE TABLE IF NOT EXISTS datacenter_uses_cooling_technologies (
    id INTEGER PRIMARY KEY DEFAULT nextval('id_seq_datacenter_uses_cooling_technologies'),
    datacenter_id INTEGER REFERENCES datacenters(id),
    cooling_technology_id INTEGER REFERENCES cooling_technologies(id),
);
-- ALTER TABLE "estimations"
-- ADD FOREIGN KEY("id_datacenter") REFERENCES "datacenters"("id")
-- ON UPDATE NO ACTION ON DELETE NO ACTION;
CREATE SEQUENCE id_seq_estimation_has_been_made_for_datacenter START 1;
CREATE TABLE IF NOT EXISTS estimation_has_been_made_for_datacenter (
    id INTEGER PRIMARY KEY DEFAULT nextval('id_seq_estimation_has_been_made_for_datacenter'),
    datacenter_id INTEGER REFERENCES datacenters(id),
    estimation_id INTEGER REFERENCES estimations(id),
);
-- ALTER TABLE "companies"
-- ADD FOREIGN KEY("belongs_to") REFERENCES "groups"("id")
-- ON UPDATE NO ACTION ON DELETE NO ACTION;
CREATE SEQUENCE id_seq_company_belongs_to_group START 1;
CREATE TABLE IF NOT EXISTS company_belongs_to_group (
    id INTEGER PRIMARY KEY DEFAULT nextval('id_seq_company_belongs_to_group'),
    company_id INTEGER REFERENCES companies(id),
    group_id INTEGER REFERENCES groups(id),
);

CREATE SEQUENCE id_seq_datacenter_is_on_land_plots START 1;
CREATE TABLE IF NOT EXISTS datacenter_is_in_campus (
    id INTEGER PRIMARY KEY DEFAULT nextval('id_seq_datacenter_is_on_land_plots'),
    datacenter_id INTEGER REFERENCES datacenters(id),
    campus_id INTEGER REFERENCES campuses(id),
);
-- ALTER TABLE "datacenters"
-- ADD FOREIGN KEY("campus") REFERENCES "campuses"("id")
-- ON UPDATE NO ACTION ON DELETE NO ACTION;
CREATE SEQUENCE id_seq_datacenter_is_in_campus START 1;
CREATE TABLE IF NOT EXISTS datacenter_is_in_campus (
    id INTEGER PRIMARY KEY DEFAULT nextval('id_seq_datacenter_is_in_campus'),
    datacenter_id INTEGER REFERENCES datacenters(id),
    campus_id INTEGER REFERENCES campuses(id),
);
-- ALTER TABLE "datacenters"
-- ADD FOREIGN KEY("electricity_generation") REFERENCES "electricity_generation_source"("id")
-- ON UPDATE NO ACTION ON DELETE NO ACTION;
CREATE SEQUENCE id_seq_datacenter_uses_electricity_generation START 1;
CREATE TABLE IF NOT EXISTS datacenter_uses_electricity_generation (
    id INTEGER PRIMARY KEY DEFAULT nextval('id_seq_datacenter_uses_electricity_generation'),
    datacenter_id INTEGER REFERENCES datacenters(id),
    campus_id INTEGER REFERENCES campuses(id),
);

CREATE SEQUENCE id_seq_datacenter_is_in_progress_state START 1;
CREATE TABLE IF NOT EXISTS datacenter_is_in_progress_state (
    id INTEGER PRIMARY KEY DEFAULT nextval('id_seq_datacenter_is_in_progress_state'),
    datacenter_id INTEGER REFERENCES datacenters(id),
    progress_step_id INTEGER REFERENCES progress_steps(id),
);

The complete schema SQL definition of the database can be found in the schema.sql file.