OTP verification for orders - create a separate table for OTP, or add columns to the orders table?

I need to add OTP verification mechanism either by SMS or Email.

The OTP is sent right before the user places the order.

My question is regarding the DB design - should it be inside the orders table - so I will add the columns: code for the otp itself, is_verified to check if the order is verified, and expiry_time for the otp.

Or, it should be completely detached from the orders, so in the frontend the user will be presented a page to confirm the OTP, and only if he confirms it he can continue to the next step which is to place the order, thus making the orders completely not related to the OTP itself?

LaryAI

3 years ago

Level 58

It is generally better to keep concerns separated, so it would be a good idea to create a separate table for OTPs. This way, the OTP verification mechanism can be reused for other parts of the application if needed, and it also keeps the orders table clean and focused on order-related information.

The OTP table could have columns such as id, code, is_verified, expiry_time, and order_id (if you want to associate the OTP with a specific order). When a user places an order, a new OTP record can be created with a randomly generated code and an expiry time. The code can be sent to the user via SMS or email, and the user can then enter the code on a separate OTP verification page. Once the code is verified, the is_verified column can be set to true.

Here's an example migration for creating the OTP table:

Schema::create('otps', function (Blueprint $table) {
    $table->id();
    $table->string('code');
    $table->boolean('is_verified')->default(false);
    $table->timestamp('expiry_time');
    $table->unsignedBigInteger('order_id')->nullable();
    $table->foreign('order_id')->references('id')->on('orders')->onDelete('cascade');
    $table->timestamps();
});

And here's an example of how to create a new OTP record when a user places an order:

$order = new Order([...]);
$order->save();

$otp = new Otp([
    'code' => generateRandomCode(),
    'expiry_time' => now()->addMinutes(10),
    'order_id' => $order->id,
]);
$otp->save();

// Send the OTP code to the user via SMS or email

Note that the generateRandomCode() function would need to be implemented separately.

1 like