sql: create database schema

1 files changed, 49 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