# UGE / L2 / Intro to relational databases / Python project prototype


## Background and goals

The introductory course to relational databases ends with a project
illustrating the use of a relational database together with a simple CRUD web
application.

The Python language could be a better fit compared to PHP for this kind of
assignment as both students and the teaching staff are already familiar with
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.


## Project structure overview

* `./sql/`
  * `tables.sql`: database initialisation statements
  * `queries.sql`: annotated SQL queries (from which the DAO is derived)
* `./flake.nix`: project runtime and development environment description


## Copyright and licensing

Copyright (C) 2021 Pacien TRAN-GIRARD.

This project is distributed under the terms of European Union Public Licence
version 1.2, a copy of which is provided in `./licence.txt`.