aboutsummaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
authorpacien2021-07-24 13:05:05 +0200
committerpacien2021-07-24 13:05:05 +0200
commit5fa8d869f476323d5243111b6c6c785c83d99400 (patch)
tree5d210d4e12d5cd5b91d78317db4e58d2ff8031be /sql
parent14f70e92e249f56ecf413fa8f262cb6120ea3350 (diff)
downloaduge_l2_rdbms_python_proto-5fa8d869f476323d5243111b6c6c785c83d99400.tar.gz
sql: create database schema
Diffstat (limited to 'sql')
-rw-r--r--sql/tables.sql43
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 @@
1-- UGE / L2 / Intro to relational databases / Python project prototype
2-- Author: Pacien TRAN-GIRARD
3-- Licence: EUPL-1.2
4
5-- Fixed precision type for our one fictive currency.
6-- https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_money
7create domain simoleon as numeric(11, 2);
8
9-- Minimalist modelling of user accounts.
10-- In this simple application, the notion of user, account and wallet/balance
11-- are merged into a single table instead of trivial 1-1 relationships.
12-- Balance consistency with other accounts and financial transaction logs will
13-- have to be ensured through SQL transactions.
14create table accounts (
15 id int generated always as identity primary key,
16 username text unique not null,
17 password_hash text not null,
18 balance simoleon default 0 not null check (balance >= 0)
19);
20
21-- Enumeration of the different types of logged financial operations.
22create type operation_type as enum (
23 'fee',
24 'transfer',
25 'deposit',
26 'withdrawal'
27);
28
29-- Minimalist, combined financial transaction log.
30-- Thanks to the similarity of the fields for the various operation types, it
31-- is possible to use a combined transaction log with a discriminant enum and
32-- a few nullity constraints. This allows for simpler queries without the need
33-- to consolidate and aggregate across multiple tables.
34create table transactions (
35 id bigint generated always as identity primary key,
36 datetime timestamp default transaction_timestamp() not null,
37 operation operation_type not null,
38 source int references accounts check
39 ((operation = 'deposit' or source is not null) and source <> recipient),
40 recipient int references accounts check
41 (operation in ('fee', 'withdrawal') or recipient is not null),
42 amount simoleon not null check (amount > 0)
43);