sql: create database schema

1 files changed, 43 insertions, 0 deletions

diff --git a/sql/tables.sql b/sql/tables.sql
new file mode 100644
index 0000000..d56156b
--- /dev/null
+++ b/sql/tables.sql
@@ -0,0 +1,43 @@
+-- UGE / L2 / Intro to relational databases / Python project prototype
+-- Author: Pacien TRAN-GIRARD
+-- Licence: EUPL-1.2
+
+-- Fixed precision type for our one fictive currency.
+-- https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_money
+create domain simoleon as numeric(11, 2);
+
+-- Minimalist modelling of user accounts.
+-- In this simple application, the notion of user, account and wallet/balance
+-- are merged into a single table instead of trivial 1-1 relationships.
+-- Balance consistency with other accounts and financial transaction logs will
+-- have to be ensured through SQL transactions.
+create table accounts (
+  id int generated always as identity primary key,
+  username text unique not null,
+  password_hash text not null,
+  balance simoleon default 0 not null check (balance >= 0)
+);
+
+-- Enumeration of the different types of logged financial operations.
+create type operation_type as enum (
+  'fee',
+  'transfer',
+  'deposit',
+  'withdrawal'
+);
+
+-- Minimalist, combined financial transaction log.
+-- Thanks to the similarity of the fields for the various operation types, it
+-- is possible to use a combined transaction log with a discriminant enum and
+-- a few nullity constraints. This allows for simpler queries without the need
+-- to consolidate and aggregate across multiple tables.
+create table transactions (
+  id bigint generated always as identity primary key,
+  datetime timestamp default transaction_timestamp() not null,
+  operation operation_type not null,
+  source int references accounts check
+    ((operation = 'deposit' or source is not null) and source <> recipient),
+  recipient int references accounts check
+    (operation in ('fee', 'withdrawal') or recipient is not null),
+  amount simoleon not null check (amount > 0)
+);