Create unauthenticated user table

Let's create an unauthenticated user table. After Creating Membership Registration Form, the next step is to create an unauthenticated user table in the database.

Unauthenticated user table

The unauthenticated user table is a table used to register temporary registered users who have not been authenticated.

When the membership registration form is submitted, the information of the e-mail address and password will be registered, but the user authentication by the e-mail address has not been completed yet. The unauthenticated user table registers users in this state.

I named the table "user_unauth".

Field	Table field name	Type	Constraint	Default value
ID	id	32bit integer	Primary key, auto-increment	None
Email address	mail	String	Non-null	Empty string
Hashed password	password_crypted	String	Non-null	Empty string
token	token	string	non-null, unique	empty string
Token expiration	token_expiration	64-bit integer	non-null	0
Creation date	token_expiration	Date Time	Non-null	0001-01-01 00:00:00 td>
Updated	token_expiration	Date Time	Non-null	0001-01-01 00:00:00 td>

If there are other input items on the membership registration form such as name, address, phone number, desired conditions, etc., that field is also required.

Create unauthenticated user table in MariaDB

Let's create an unauthenticated user table with MariaDB.

create table user_unauth (
  id int primary key auto_increment,
  mail varchar (150) not null default'',
  password_crypted varchar (150) not null default'',
  token varchar (150) not null default'',
  token_expiration bigint not null default 0,
  created_at datetime not null default '0001-01-01 00:00:00',
  updated_at datetime not null default '0001-01-01 00:00:00',
  unique (mail),
  unique (token)
) ENGINE = InnoDB CHARSET = utf8mb4;

It is assumed that utf8mb4 is supported. If it is not supported, please use utf8. The database engine is InnoDB to support transactions.

Put as many fields as possible with a not null constraint. Set all default values for fields other than the primary key.

Field description

A description of the field.

ID

In the introduction to web system development, ID means a key that can uniquely identify a row. An integer value that has no meaning in itself. Make sure every table has an ID whose value is automatically incremented. If it is referenced from another table, use the ID.

Set the primary key and auto_increment. Primary key constraints are special constraints that can be set on only one field in a table, ensuring uniqueness and non-nullity for the field.

The type is a 32-bit integer type int.

Hashed password

A field to store the hashed password. Just keep in mind here that you don't just save the password you received on the form. Hashed passwords are discussed in the places where we describe the process of user authentication.

Email address

The user's email address. Email addresses must be unique, so set a unique constraint.

Token

A unique token issued to send an email and authenticate users. It is a random value, such as "356a192b7913b04c54574d18c28d46e6395428ab". Tokens must be unique, so set a unique constraint.

Token expiration date

The expiration date of the token. When this expiration date expires, the token becomes invalid and user authentication is no longer possible.

The type is a 64-bit integer type bigint so that the time information can be retained for a long time.

Creation date and time

The creation date and time is of type datetime. The default value is "0001-01-01 00:00:00", which is the minimum valid date and time in the SQL standard.

Updated date and time

The update date and time is of type datetime. The default value is "0001-01-01 00:00:00", which is the minimum valid date and time in the SQL standard.

Perl Web App Development Tutorial