1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
-- 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)
);
-- Additional indexes for the financial transaction log for the common
-- participant-centered queries. Those also include the datetime column, which
-- is useful for paginating the log.
create index transaction_source_index on transactions (source, datetime);
create index transaction_recipient_index on transactions (recipient, datetime);
|