How to design a secure MySQL table structure to implement payment functions?

How to design a secure MySQL table structure to implement payment function?

With the rapid development of e-commerce, payment function has become one of the core requirements of websites and applications. Designing a secure MySQL table structure is an important step in ensuring the reliability and security of payment functions. This article will introduce how to design a secure MySQL table structure to implement payment functions and provide specific code examples.

1. User table design:

In order to implement the payment function, you first need to design a user table to save the user's basic information. The user table needs to contain at least the following fields:

• id: unique user identifier
• username: username
• password: password (encrypted storage required)
• email: Email
• phone: Mobile phone number
• balance: Account balance

SQL statement example to create user table:

CREATE TABLE `user` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(20) NOT NULL,
  `balance` DECIMAL(10, 2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Order table design:

Next, you need to design an order table to record the user's payment order information. The order table must contain at least the following fields:

• id: the unique identifier of the order
• user_id: the id field of the associated user table, indicating the order user
• order_no: Order number
• amount: Order amount
• status: Order status (to be paid, paid, canceled, etc.)

SQL statement example to create an order table:

CREATE TABLE `order` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(10) UNSIGNED NOT NULL,
  `order_no` VARCHAR(50) NOT NULL,
  `amount` DECIMAL(10, 2) NOT NULL,
  `status` ENUM('pending', 'paid', 'cancelled') NOT NULL DEFAULT 'pending',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. Payment record table design:

In order to record the user's payment history, a payment record table needs to be designed to store payment details. The payment record table must contain at least the following fields:

• id: the unique identifier of the payment record
• order_id: the id field of the associated order table, indicating the paid order
• payment_method: payment method (Alipay, WeChat payment, etc.)
• payment_time: payment time
• payment_status: payment status (success, failure, etc.)

Create payment record table SQL statement examples:

CREATE TABLE `payment` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT(10) UNSIGNED NOT NULL,
  `payment_method` VARCHAR(50) NOT NULL,
  `payment_time` DATETIME NOT NULL,
  `payment_status` ENUM('success', 'failed') NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`order_id`) REFERENCES `order`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. Database connection and operation examples:

After designing the table structure, related data operations need to be performed through database connections. The following is a basic database connection and sample code for inserting data:

<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "payment";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $conn->prepare("INSERT INTO `user` (username, password, email, phone) VALUES (:username, :password, :email, :phone)");
    $stmt->bindParam(':username', $username);
    $stmt->bindParam(':password', $password);
    $stmt->bindParam(':email', $email);
    $stmt->bindParam(':phone', $phone);

    // 设置参数
    $username = "test";
    $password = password_hash("123456", PASSWORD_DEFAULT);
    $email = "test@example.com";
    $phone = "1234567890";

    $stmt->execute();

    echo "插入数据成功";
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;
?>

The above code sample demonstrates how to create a user and insert related information into the user table.

Summary:

Through the above steps, we designed a secure MySQL table structure to implement the payment function. This table structure includes user table, order table and payment record table. Through correct table structure design, the reliability and security of the payment function can be ensured. At the same time, according to specific business needs, the table structure can be appropriately expanded and optimized according to the actual situation.

The above is the detailed content of How to design a secure MySQL table structure to implement payment functions?. For more information, please follow other related articles on the PHP Chinese website!