Where to Discuss?

Local Group

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]>

User Management: MariaDB CLI

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]> 

User Management: MariaDB CLI

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]> 

User Management: MariaDB CLI


Using PHPMyAdmin

We can continue inserting fixture:

Insert Role

User Management: PHPMyAdmin: Role Insert

User Management: PHPMyAdmin: Role Insert

Insert The Rest

User Management: PHPMyAdmin: Permission

User Management: PHPMyAdmin: Role Permission

User Management: PHPMyAdmin: User Role Permission


CREATE INDEX

We can also add index to each tables. For example for user in MariaDB CLI.

User Management: 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.

User Management: PHPMyAdmin: Index: Role Permission

User Management: PHPMyAdmin: Index: Role Permission

And also the rest:

User Management: PHPMyAdmin: Index: Role Permission

User Management: PHPMyAdmin: Index: User Role Permission


All Tables

Your MariaDB should have all these tables:

User Management: PHPMyAdmin: Console

Your PHPMyAdmin should also have all these tables:

User Management: PHPMyAdmin: Console

It is fun to see the relationship in Designer.

User Management: PHPMyAdmin: Console

We can also check the console in PHPMyAdmin

User Management: PHPMyAdmin: Console

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?