Introduction

Every application requires a data source. In this post, I will create a new database with a table that implements member account management.

I will use this to create a login function for the application and to present a logged-in user with a list of features as defined by their membership profile.

MySQL

When modeling, I use MySQL Community Edition as my database. It’s cross-platform, well documented, and is licensed for use without fees. For projects that require an embedded database, I choose SQLite.

You may use a different database if you prefer - just ensure that it implements a sql engine so you can follow along with today’s lesson.

Installation

If MySQL is not already installed in your environment, download & install it now. Start at the link below to locate the package for your OS, download and install it.

https://dev.mysql.com/downloads/mysql/

Note that you do not need to create an account to download the packages. Click the ‘No thanks, just start my download’ link unless you really want an account.

Once installed, you should be able to login to your local mysql server using whatever user you created. That could be anything, so I will not cover that here.

MySQL Admin User

In most posts covering mysql installation, it will be advised not to use the default database user root. Be sure to follow the instructions to create a new user & provide it with privileges to create databases. In the official MySQL documentation, you will see instructions for keeping the default root user after securing it with a strong password.

You must decide what your security risk profile is and use that as guidance. For modeling on a developer laptop, keeping the root user with a strong password is probably okay. Production databases should not use the root user.

In my example here, I have created a user named developer with a password and have logged in as that user.

/Users/jbminn\>mysql -u developer -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Database Naming

I’ll first create a database:

mysql> CREATE DATABASE members;
Query OK, 1 row affected (0.02 sec)

mysql>

You’ll notice that I used uppercase letters for the mysql statements and lowercase for the unique value of the database name. This is a convention only & may make commands easier to understand as you read them in documentation. You can use all lowercase letters if you wish.

Now I will create the accounts table inside the members database.

This can done inside the mysql console but that can be a bit tedious. If I make a typo, I generally have to start over. If I instead create the structure of the table in a script, I can pass that script into a mysql session that is initiated from the command line.

For this exercise, I created a file named

accounts_table.sql

containing these lines:

/Users/jbminn\>cat accounts_table.sql 
USE members;
CREATE TABLE accounts (
 account_id INT PRIMARY KEY AUTO_INCREMENT,
 member_name VARCHAR(200) NOT NULL UNIQUE,
 email VARCHAR(200) NOT NULL UNIQUE,
 password VARCHAR(150) NOT NULL,
 account_type VARCHAR(200),
 account_creation DATETIME,
 account_modification_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 account_status VARCHAR(20),
 role CHAR(25));

These are the columns, or fields, that my design requires in the accounts table. When I later use this to manage a member’s account & access, these fields will contain values that the application uses to authenticate the member & determine the correct set of services for which she is authorized. Fields that define account type & role are used to implement user profiles, which you can use later to control access to other features.

In the command below, mysql first logs in with the credentials provided - in this case, the developer user and password - then commands in the passed-in script will be executed as though they were typed into the mysql console. This can be a convenient way to create tables using automation.

I use this command:

/Users/jbminn\>mysql < accounts_table.sql -u developer -p
Enter password: 

If there are no errors, the accounts table will be created and the prompt returns. I then do a quick check to ensure the table was created using these commands:

/Users/jbminn\>mysql -u developer -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use members;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc accounts;
+---------------------------+--------------+------+-----+---------+-----------------------------+
| Field                     | Type         | Null | Key | Default | Extra                       |
+---------------------------+--------------+------+-----+---------+-----------------------------+
| account_id                | int          | NO   | PRI | NULL    | auto_increment              |
| member_name               | varchar(200) | NO   | UNI | NULL    |                             |
| email                     | varchar(200) | NO   | UNI | NULL    |                             |
| password                  | varchar(150) | NO   |     | NULL    |                             |
| account_type              | varchar(200) | YES  |     | NULL    |                             |
| account_creation          | datetime     | YES  |     | NULL    |                             |
| account_modification_time | timestamp    | YES  |     | NULL    | on update CURRENT_TIMESTAMP |
| account_status            | varchar(20)  | YES  |     | NULL    |                             |
| role                      | char(25)     | YES  |     | NULL    |                             |
+---------------------------+--------------+------+-----+---------+-----------------------------+
9 rows in set (0.01 sec)

mysql>

First, I login to mysql, then select the database with the use statement. Finally, I run the desc statement on the accounts table.

These actions confirm that:

• my database has the credentials I expect
• my database holds an accounts table
• the structure of the accounts table is what I intended

Up Next

I’m now ready to use this database to create a login function for my application.