sql: create database schema

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.
 To showcase Python as a platform suitable for this purpose, a small demo
 project specified in `./demo-project-specs.md` will be implemented.
 
+The present document aims to motivate the functional and technical choices
+which have been made.
+
+
+## Interpretation of the specifications
+
+Undefined aspects of the specifications are clarified as follows, with the
+intent of keeping the project as simple as possible:
+
+* the platform only deals with one currency ([simoleons], arbitrarily),
+* the platform only supports one language (English, arbitrarily),
+* each user only has one account/wallet/balance (all three terms synonymous),
+* there is no notion of financial order (transactions are immediate),
+* only the effective transactions are logged, the rejected ones are not,
+* fees are billed on top of internal transfers instead of taking a cut.
+
+[simoleons]: https://en.wiktionary.org/wiki/simoleon
+
+
+## Database model
+
+### Entity-relationship model
+
+The database model is fully described as types, tables and constraints in
+`./sql/tables.sql`. The file is annotated with comments and remarks explaining
+the design choices and highlights some additional constraints which remain to
+be enforced at upper levels.
+
+An entity-relationship diagram of the model can be generated from the SQL table
+descriptions using a tool like [SQLFairy] (_note: SQLFairy [crashes] with
+recent PostgreSQL dialects, so it is not usable here_).
+
+[SQLFairy]: http://sqlfairy.sourceforge.net/
+[crashes]: https://rt.cpan.org/Public/Bug/Display.html?id=138045
+
+### Consistency
+
+The consistency of the account balance and the financial transaction log can
+be ensured through multiple means such as executing modification and insertions
+in SQL transactions at the application level, or using triggers within the
+database itself. The former option is chosen over the latter to avoid implicit
+actions.
+
+Additional constraints to prevent alterations of the financial transaction log
+such as deletions are omitted here as the web application will act as the sole
+gateway to interact with the database and will offer no way of doing this kind
+of operations. For the same reason, the use of stored procedures was not
+retained.
+
 
 ## Copyright and licensing
 
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)
+);