Preface
Goal: Starting from Requirement.
Before making prototype. We need to make sure that this initial SQL is valid.
Prerequisite
We need MmySQL compatible in CLI or PHPMyAdmin. For this two to work, we need a LAMP stack, DAMP stack (docker) or XAMPP. I don’t know about Mac. I never have one. I have made a note on how to setup LAMP or DAMP. You might find them interesting.
I’m using MariaDB for backend. You may use other.
Using MariaDB CLI
First we need to log in as root to create database. For a better solution, we should set privileges for specific user. But this time I’m going to make this simple.
Login
We need to log in as root to create database.
❯ sudo mariadb -u root -p
[sudo] password for epsi:
Enter password:
MariaDB [(none)]> \! reset
CREATE DATABASE and USE
We should create the database first.
MariaDB [(none)]> CREATE DATABASE Blewah;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> USE Blewah;
Database changed
MariaDB [Blewah]>
CREATE TABLE and INSERT
We can continue, creating table.
MariaDB [Blewah]> CREATE TABLE User (
-> id INT PRIMARY KEY,
-> username VARCHAR(255) NOT NULL,
-> password VARCHAR(255) NOT NULL,
-> email VARCHAR(255) NOT NULL
-> );
Query OK, 0 rows affected (0.011 sec)
MariaDB [Blewah]>
We can continue, creating table, also insert table.
MariaDB [Blewah]> INSERT INTO User (id, username, password, email)
-> VALUES
-> (1, 'alice', '[hashed password]', 'alice@example.com'),
-> (2, 'bob', '[hashed password]', 'bob@example.com'),
-> (3, 'charlie', '[hashed password]', 'charlie@example.com');
Query OK, 3 rows affected (0.002 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [Blewah]>
Using PHPMyAdmin
We can continue inserting fixture:
Insert Role
Insert The Rest
CREATE INDEX
We can also add index to each tables. For example for user in MariaDB CLI.
MariaDB [Blewah]> CREATE INDEX idx_user_username ON User (username);
Query OK, 0 rows affected (0.021 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [Blewah]> CREATE INDEX idx_user_email ON User (email);
Query OK, 0 rows affected (0.020 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [Blewah]>
Then we can continue to create index for role and permission in PHPMyAdmin.
And also the rest:
All Tables
Your MariaDB should have all these tables:
Your PHPMyAdmin should also have all these tables:
It is fun to see the relationship in Designer.
We can also check the console in PHPMyAdmin
I guess all SQL statements are valid.
Conclusion
Thank you for reading. We are not done. That is not all. We are not finished yet.
Maybe another article, another time. What do you think?