Using MySQL Magic Numbers with If and Case
Introduction
When designing applications in MySQL, you'll encounter scenarios that require heavy use of magic numbers. These are constant values that won't immediately make sense to anyone with little knowledge of how your database works. For instance, assume you're creating a microfinance bank database. If you've got a table named charges that can either have penalties or fees, you might use the characters P and F to denote the information in a column named charge_type.
For this information to be useful, you must spell out the magic numbers when presenting the information to the frontend desktop or mobile applications. This is where the MySQL IF and CASE statements come into play. In this guide, you will use the MySQL IF and CASE statements to run logical tests against different table values to spell out magic numbers in a human-readable format in a hypothetical microfinance bank database running on Ubuntu 20.04.
Prerequisites
Before you proceed, you'll require the following:
- An Ubuntu 20.04 server.
- A sudo user.
- A LAMP Stack.
Create a Test microfinance Database
SSH to your server and run the command below to log in to the MySQL server as root.
$ sudo mysql -u root -pEnter the root password of your MySQL server and press Enter to continue. Once you get the mysql> prompt, run the command below to create a microfinance database.
mysql> CREATE DATABASE microfinance;Switch to the microfinance database.
mysql> USE microfinance;Next, create a charges table. You'll define some columns using the MySQL CHAR() data type in this table. This column will hold some magic numbers.
mysql> CREATE TABLE charges
(
charge_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
applies_to CHAR(1),
charge_type CHAR(1),
charge_name VARCHAR(50),
frequency CHAR(1),
amount DECIMAL(17,2)
) ENGINE = InnoDB;Insert some records to the charges table so that you can begin exploring how the MySQL IF and CASE functions work. For the basis of this tutorial, you'll use the following magic numbers when populating the charges table.
The applies_to column:
C- CLIENTS ACCOUNTSS- SAVINGS ACCOUNTSI- INVESMENT/SHARES ACCOUNTSL- LOAN ACCOUNTS
The charge_type column:
F- FEEP- PENALTY
The frequency column:
O- ONE TIMER- RECURRING
Begin by entering a one-time registration fee for the CLIENTS accounts.
mysql> INSERT INTO charges(applies_to, charge_type, charge_name, frequency, amount) VALUES ('C', 'F', 'REGISTRATION FEE', 'O', '500');Next, add a one-time SAVINGS ACCOUNTS withdrawal fee, a recurring monthly ledger fee, and a one-time lateness penalty.
mysql> INSERT INTO charges(applies_to, charge_type, charge_name, frequency, amount) VALUES ('S', 'F', 'WITHDRAWAL FEE', 'O', '10');
mysql> INSERT INTO charges(applies_to, charge_type, charge_name, frequency, amount) VALUES ('S', 'F', 'MONTHLY LEDGER FEE', 'R', '50');
mysql> INSERT INTO charges(applies_to, charge_type, charge_name, frequency, amount) VALUES ('S', 'P', 'LATENESS PENALTY', 'O', '5');Then, enter a one-time redemption fee and purchase fee for the SHARES/INVESTMENTS ACCOUNTS.
mysql> INSERT INTO charges(applies_to, charge_type, charge_name, frequency, amount) VALUES ('I', 'F', 'REDEMTION FEE', 'O', '10');
mysql> INSERT INTO charges(applies_to, charge_type, charge_name, frequency, amount) VALUES ('I', 'F', 'PURCHASE FEEE', 'O', '50');Finally, insert a one-time application fee and a recurring lateness penalty for the LOANS ACCOUNTS.
mysql> INSERT INTO charges(applies_to, charge_type, charge_name, frequency, amount) VALUES ('L', 'F', 'APPLICATION FEE', 'O', '10');
mysql> INSERT INTO charges(applies_to, charge_type, charge_name, frequency, amount) VALUES ('L', 'P', 'LATENESS PENALTY','R', '50');After running each INSERT command above, ensure you get the output below.
...
Query OK, 1 row affected (0.04 sec)Query the charges table to make sure the data is in place.
mysql> SELECT
charge_id,
applies_to,
charge_type,
charge_name,
frequency,
amount
FROM charges;As you can see from the output below, the raw data might not make sense to someone who is not familiar with the meaning of your magic numbers. This design pattern is great when it comes to saving disk space, optimizing and speeding up queries in your database.
+-----------+------------+-------------+--------------------+-----------+--------+
| charge_id | applies_to | charge_type | charge_name | frequency | amount |
+-----------+------------+-------------+--------------------+-----------+--------+
| 1 | C | F | REGISTRATION FEE | O | 500.00 |
| 2 | S | F | WITHDRAWAL FEE | O | 10.00 |
| 3 | S | F | MONTHLY LEDGER FEE | R | 50.00 |
| 4 | S | P | LATENESS PENALTY | O | 5.00 |
| 5 | I | F | REDEMTION FEE | O | 10.00 |
| 6 | I | F | PURCHASE FEEE | O | 50.00 |
| 7 | L | F | APPLICATION FEE | O | 10.00 |
| 8 | L | P | LATENESS PENALTY | R | 50.00 |
+-----------+------------+-------------+--------------------+-----------+--------+
8 rows in set (0.00 sec)In the next step, you'll use the MySQL IF and CASE statements to spell out those magic numbers for reporting purposes.
Spell Out the Magic Numbers
In MySQL, you can use the IF statement to evaluate a value stored in a column and return different results depending on whether the evaluation results to true or false. Use the general syntax below when using an IF statement in MySQL.
mysql> IF ([EVALUATION], [VALUE IF TRUE], [VALUE IF FALSE];To put this in a better perspective, run the command below to spell out the full string values(That is, either FEE or PENALTY) for the charge_type column in your charges table.
mysql> SELECT
charge_id,
applies_to,
IF (charge_type = 'F', 'FEE', 'PENALTY') AS charge_type,
charge_name,
frequency,
amount
FROM charges;As you might have noted from the output below, the information displayed in the charge_type column is now human-readable and is no longer ambiguous.
+-----------+------------+-------------+--------------------+-----------+--------+
| charge_id | applies_to | charge_type | charge_name | frequency | amount |
+-----------+------------+-------------+--------------------+-----------+--------+
| 1 | C | FEE | REGISTRATION FEE | O | 500.00 |
| 2 | S | FEE | WITHDRAWAL FEE | O | 10.00 |
| 3 | S | FEE | MONTHLY LEDGER FEE | R | 50.00 |
| 4 | S | PENALTY | LATENESS PENALTY | O | 5.00 |
| 5 | I | FEE | REDEMTION FEE | O | 10.00 |
| 6 | I | FEE | PURCHASE FEEE | O | 50.00 |
| 7 | L | FEE | APPLICATION FEE | O | 10.00 |
| 8 | L | PENALTY | LATENESS PENALTY | R | 50.00 |
+-----------+------------+-------------+--------------------+-----------+--------+
8 rows in set (0.00 sec)Apply the same syntax to the frequency column and spell out the full string values for the columns (either ONE TIME or RECURRING).
mysql> SELECT
charge_id,
applies_to,
IF (charge_type = 'F', 'FEE', 'PENALTY') AS charge_type,
charge_name,
IF (frequency = 'O', 'ONE TIME', 'RECURRING') AS frequency,
amount
FROM charges;The frequency column is now spelled out, as shown below.
+-----------+------------+-------------+--------------------+-----------+--------+
| charge_id | applies_to | charge_type | charge_name | frequency | amount |
+-----------+------------+-------------+--------------------+-----------+--------+
| 1 | C | FEE | REGISTRATION FEE | ONE TIME | 500.00 |
| 2 | S | FEE | WITHDRAWAL FEE | ONE TIME | 10.00 |
| 3 | S | FEE | MONTHLY LEDGER FEE | RECURRING | 50.00 |
| 4 | S | PENALTY | LATENESS PENALTY | ONE TIME | 5.00 |
| 5 | I | FEE | REDEMTION FEE | ONE TIME | 10.00 |
| 6 | I | FEE | PURCHASE FEEE | ONE TIME | 50.00 |
| 7 | L | FEE | APPLICATION FEE | ONE TIME | 10.00 |
| 8 | L | PENALTY | LATENESS PENALTY | RECURRING | 50.00 |
+-----------+------------+-------------+--------------------+-----------+--------+
8 rows in set (0.00 sec)The IF function is mostly suitable for column names that contain utmost two different values unless you opt to use nested statements. If you're evaluating more than two values, the MySQL CASE statement is suitable for that purpose. The following is a basic syntax of the MySQL CASE statement.
mysql> CASE
WHEN [EVALUATION ONE] THEN [VALUE IF EVALUATION ONE IS TRUE]
WHEN [EVALUATION TWO] THEN [VALUE IF EVALUATION TWO IS TRUE]
WHEN [EVALUATION N] THEN [VALUE IF EVALUATION N IS TRUE]
ELSE [VALUE FOR ANY OTHER CONDITION NOT LISTED ABOVE]
END AS [NEW COLUMN VALUE]For instance, to spell out the full string values for the applies_to column in the charges table, use the command below.
mysql> SELECT
charge_id,
CASE
WHEN applies_to = 'C' THEN 'CLIENTS ACCOUNTS'
WHEN applies_to = 'S' THEN 'SAVINGS ACCOUNTS'
WHEN applies_to = 'I' THEN 'SHARES/INVESMENTS ACCOUNTS'
ELSE 'LOANS ACCOUNTS'
END AS applies_to,
IF (charge_type = 'F', 'FEE', 'PENALTY') AS charge_type,
charge_name,
IF (frequency = 'O', 'ONE TIME', 'RECURRING') AS frequency,
amount
FROM charges;The MySQL CASE statement should work as expected as you can confirm from the following output.
+-----------+----------------------------+-------------+--------------------+-----------+--------+
| charge_id | applies_to | charge_type | charge_name | frequency | amount |
+-----------+----------------------------+-------------+--------------------+-----------+--------+
| 1 | CLIENTS ACCOUNTS | FEE | REGISTRATION FEE | ONE TIME | 500.00 |
| 2 | SAVINGS ACCOUNTS | FEE | WITHDRAWAL FEE | ONE TIME | 10.00 |
| 3 | SAVINGS ACCOUNTS | FEE | MONTHLY LEDGER FEE | RECURRING | 50.00 |
| 4 | SAVINGS ACCOUNTS | PENALTY | LATENESS PENALTY | ONE TIME | 5.00 |
| 5 | SHARES/INVESMENTS ACCOUNTS | FEE | REDEMTION FEE | ONE TIME | 10.00 |
| 6 | SHARES/INVESMENTS ACCOUNTS | FEE | PURCHASE FEEE | ONE TIME | 50.00 |
| 7 | LOANS ACCOUNTS | FEE | APPLICATION FEE | ONE TIME | 10.00 |
| 8 | LOANS ACCOUNTS | PENALTY | LATENESS PENALTY | RECURRING | 50.00 |
+-----------+----------------------------+-------------+--------------------+-----------+--------+
8 rows in set (0.00 sec)As you might have noted from the outputs of all the SELECT commands that you've run, the IF and CASE statements are great for spelling out the magic numbers when it comes to displaying actual string names for abbreviated column values.
Conclusion
In this guide, you've used the MySQL IF and CASE statements to spell out magic numbers in a hypothetical microfinance database. Adopt the code in this guide when creating your next MySQL project.