aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--readme.md49
-rw-r--r--sql/tables.sql43
2 files changed, 92 insertions, 0 deletions
diff --git a/readme.md b/readme.md
index b75e14a..3c53966 100644
--- a/readme.md
+++ b/readme.md
@@ -14,6 +14,55 @@ the former from preceding units.
14To showcase Python as a platform suitable for this purpose, a small demo 14To showcase Python as a platform suitable for this purpose, a small demo
15project specified in `./demo-project-specs.md` will be implemented. 15project specified in `./demo-project-specs.md` will be implemented.
16 16
17The present document aims to motivate the functional and technical choices
18which have been made.
19
20
21## Interpretation of the specifications
22
23Undefined aspects of the specifications are clarified as follows, with the
24intent of keeping the project as simple as possible:
25
26* the platform only deals with one currency ([simoleons], arbitrarily),
27* the platform only supports one language (English, arbitrarily),
28* each user only has one account/wallet/balance (all three terms synonymous),
29* there is no notion of financial order (transactions are immediate),
30* only the effective transactions are logged, the rejected ones are not,
31* fees are billed on top of internal transfers instead of taking a cut.
32
33[simoleons]: https://en.wiktionary.org/wiki/simoleon
34
35
36## Database model
37
38### Entity-relationship model
39
40The database model is fully described as types, tables and constraints in
41`./sql/tables.sql`. The file is annotated with comments and remarks explaining
42the design choices and highlights some additional constraints which remain to
43be enforced at upper levels.
44
45An entity-relationship diagram of the model can be generated from the SQL table
46descriptions using a tool like [SQLFairy] (_note: SQLFairy [crashes] with
47recent PostgreSQL dialects, so it is not usable here_).
48
49[SQLFairy]: http://sqlfairy.sourceforge.net/
50[crashes]: https://rt.cpan.org/Public/Bug/Display.html?id=138045
51
52### Consistency
53
54The consistency of the account balance and the financial transaction log can
55be ensured through multiple means such as executing modification and insertions
56in SQL transactions at the application level, or using triggers within the
57database itself. The former option is chosen over the latter to avoid implicit
58actions.
59
60Additional constraints to prevent alterations of the financial transaction log
61such as deletions are omitted here as the web application will act as the sole
62gateway to interact with the database and will offer no way of doing this kind
63of operations. For the same reason, the use of stored procedures was not
64retained.
65
17 66
18## Copyright and licensing 67## Copyright and licensing
19 68
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);