Going Relational
Yesterday I sat down and had some fun designing a SQL database to implement the transactional storage and procedure layer of the software system that I outlined last week. It is a relatively simple implementation, yet it covers the core points of what the database should provide — store the employees’ money accounts and allow them to conduct transactions. My current plan is to implement all minimal components so that in a couple of months I have a working software for demonstration purposes, which then can be quickly adapted and put to practical use when a client comes and decides to test the concept at their company.
The RDBMS I chose is MySQL 5.0.32. It is open source and widely used in web applications, such as this blog (run by WordPress).
I designed a database with 3 tables:
- TABLE employees to hold the login data for each employee. In a production environment one may instead have a dedicated server to manage identities and provide log-in.
- TABLE accounts to store the transactional accounts of the employees.
- TABLE transactions to log all transactions that are being made
The more important data fields of the three SQL tables. The foreign key relations are highlighted
The exact SQL spec of the three tables looks like this:
CREATE TABLE employees (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(36) NOT NULL,
login VARCHAR(12) NOT NULL,
password CHAR(40) NOT NULL,
status ENUM ('active', 'departed') NOT NULL DEFAULT 'active'
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE accounts (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
holder MEDIUMINT UNSIGNED NOT NULL,
balance DECIMAL(9,2) NOT NULL,
created DATETIME NOT NULL,
status ENUM('active', 'suspended', 'closed') NOT NULL DEFAULT 'active',
INDEX (holder),
FOREIGN KEY (holder) REFERENCES employees(id)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE transactions (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
date TIMESTAMP,
sending_account MEDIUMINT UNSIGNED NOT NULL,
receiving_account MEDIUMINT UNSIGNED NOT NULL,
amount DECIMAL(9,2) NOT NULL,
reference VARCHAR(255),
INDEX (sending_account, receiving_account),
FOREIGN KEY (sending_account) REFERENCES accounts(id)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
FOREIGN KEY (receiving_account) REFERENCES accounts(id)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)ENGINE=INNODB DEFAULT CHARSET=utf8;
Two notes regarding the SQL description of the tables:
- The foreign key relations have ON DELETE RESTRICT and ON UPDATE RESTRICT constraints in order to preserve the integrity of the records. For example, when an employee leaves the company his account data must not be deleted, otherwise the other employees may receive an incomplete account statement of their previous transactions. Instead, the status of the leaving employee record should be changed to ‘departed’ and his account flagged as ‘closed’.
- The employee.id and accounts.id fields are defined as UNSIGNED MEDIUMINT to provide ID numbers for up to 16′777′215 records. The transactions.id field is an UNSIGNED INT which can provide up to 4′294′967′295 transaction IDs. I assume this number would suffice even to a large company for at least several years: 100′000 employees x 10 transactions daily x 365 = 365 mio. transactions p.a. If not, the integer size can be increased, but this won’t be the only IT issue then
I decided to implement the transactional business logic as stored procedures within the database. The idea is to keep the ‘transactional engine’ as compact as possible by having the data and the logic close to one another. Again, there is nothing fancy to the actual transaction rules, just simple common sense code that says, for example, that before making a payment the program should check whether the employee has enough money in his account.
Here I’ll list only the make_payment() procedure, the rest are more or less of the INSERT/SELECT/UPDATE type of code. Expectedly, the procedure has to execute the transaction statements as an “atom” and makes use of the standard COMMIT and ROLLBACK commands.
# Make a payment
# returns SELECT as return_code:
# 0 on success,
# -1 on insufficient balance,
# -2 on missing/inactive sender account,
# -3 on inactive recipient account
CREATE PROCEDURE make_payment (
IN sending_account MEDIUMINT,
IN receiving_account MEDIUMINT,
IN amount DECIMAL(9,2),
IN reference VARCHAR(255)
)
proc: BEGIN
DECLARE status_result TINYINT SIGNED;
DECLARE sender_balance DECIMAL(9,2) SIGNED;
START TRANSACTION;
# Check if sending account is active/exists
SELECT COUNT(status) INTO status_result FROM accounts
WHERE accounts.id=sending_account AND
accounts.status='active';
IF (status_result != 1) THEN
ROLLBACK;
SELECT -2 AS return_code;
LEAVE proc;
END IF;
# Check if receiving account is active/exists
SELECT COUNT(status) INTO status_result FROM accounts
WHERE accounts.id=receiving_account
AND accounts.status='active';
IF (status_result != 1) THEN
ROLLBACK;
SELECT -3 AS return_code;
LEAVE proc;
END IF;
# Check if sending account has enough money for the payment
SELECT balance INTO sender_balance FROM accounts
WHERE accounts.id=sending_account LIMIT 1;
IF (sender_balance - amount < 0) THEN
ROLLBACK;
SELECT -1 AS return_code;
LEAVE proc;
END IF;
# Proceed with transaction
INSERT INTO transactions SET
sending_account=sending_account,
receiving_account=receiving_account,
amount=amount,
reference=reference;
UPDATE accounts SET balance=balance-amount
WHERE id=sending_account;
UPDATE accounts SET balance=balance+amount
WHERE id=receiving_account;
COMMIT;
SELECT 0 AS return_code;
END;
This SQL code of course isn’t final and would probably evolve further as the software development goes on.
Finally, if this programming code hasn’t scared you, you may want to have a look at these database software jokes 
