# Database Principles

This page provides a global understanding of the Chill database and explains some implementation details that help accelerate processing from the database or exploit it more easily.

!!! warning "Database Schema Stability"
    The stability of the database schema is not guaranteed.

    However, it evolves relatively little. It is rare for tables or columns to be deleted or renamed, but it is not guaranteed that this cannot happen.

## Overview

A commented list of all tables is available in CSV format at `./database/table_list.csv`.

### Schema and naming conventions

At the beginning of Chill's history, PostgreSQL schemas were not used. Data was stored in the `public` schema.

Later, new bundles appeared, and tables were classified into dedicated schemas.

Currently:

- for older bundles, those that already have tables in the public schema, new tables are added to this schema. They are prefixed with `chill_<bundle_name>_`;
- for more recent bundles, tables are created in the dedicated schema

### Historical data

Some data is historized:

- the referents of an accompanying period;
- the statuses of an accompanying period;
- the link between territories and users;
- etc.

In these cases, Chill generally creates two columns, which are usually named `startDate` and `endDate`. When the `endDate` column is `NULL`, it means that the period is not "closed". The `startDate` column is not nullable.

In some cases, the current data (referent of an accompanying period, for example) is also repeated at the table level itself. For example, the accompanying periods table `chill_person_accompanying_period` has a `step` column (the status of the period) and `user_id` (referent id) in addition to the history. Although redundant, this simplifies processing.

## Special relationships

### Users, households, addresses

Users have an address through households: in the interface, the address is recorded in the household file, and it is "given" to users who are members of the household, **and** who share the address of this household. Indeed, it is possible that users "belong" to a household without being domiciled there: this is the case, for example, for children in shared custody.

The history of users' membership in the household is preserved, as well as the history of addresses for the same household.

The tables involved are as follows:

- the `chill_person_person` table lists the users;
- the `chill_person_household_members` table lists household memberships: this is the junction between users and households:
  - the `startDate` and `endDate` columns indicate the start and end date of membership;
  - the `shareHousehold` column indicates whether the user shares the household address (if yes, its value is `TRUE`)
- the `chill_person_household` table lists households
- the `chill_person_household_to_addresses` table associates households with addresses;
- the `chill_main_address` table contains addresses, indicating the validity start date (`validFrom`) and the validity end date (`validTo`).

To simplify the resolution of addresses and users, two views have been implemented:

- the `view_chill_person_household_address` view takes up, for each user, the history of household memberships broken down by the address history of a household.
  In other words, a line is created each time a user changes household, or a household changes address. It is therefore possible to find the complete address history for a given user via this table.
- the `view_chill_person_current_address` view takes up the current address of users.

### Addresses and geographical units

Chill provides statistics on the location of addresses relative to geographical zones (`chill_main_geographical_unit`).

Since the geographical resolution of addresses is costly in CPU and processing time, a materialized view has been created: `view_chill_main_address_geographical_unit`. It is refreshed daily in the production database.

## Table list and comments

A commented list of all tables is available in CSV format at `./database/table_list.csv`.
