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||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.
A description of the field.
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.
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.
The user's email address. Email addresses must be unique, so set a unique constraint.
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.