MySQL table design tutorial to create a simple online question and answer table

MySQL table design tutorial: Create a simple online question and answer table

In today's technologically advanced society, people have higher expectations for obtaining information and solving problems. Online Q&A platforms emerged as a result and became a very popular and convenient way. In this article, we will learn how to use a MySQL database to create a simple online question and answer form.

MySQL is an open source relational database management system with good performance and stability and is widely used in applications of all sizes. Before starting to create our Q&A table, make sure you have properly installed MySQL and the corresponding client.

First, create a new database. Open the MySQL client and enter the following command:

CREATE DATABASE question_answer;

This will create a database named "question_answer". Next, we will create a table called "questions" in this database to store questions and answers.

USE question_answer;

CREATE TABLE questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_title VARCHAR(100) NOT NULL,
    question_body TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

In the above code, we define the structure of the "questions" table. It contains the following columns:

• The "id" column is an auto-incrementing integer used as a unique identifier for each question.
• The "question_title" column is a string of no more than 100 characters in length, used to store the title of the question.
• The "question_body" column is a text type used to store a detailed description of the question.
• The "created_at" column is a timestamp type, used to record the creation time of the issue.
• The "modified_at" column is also a timestamp type, used to record the modification time of the issue. When updating a question, this column is automatically updated to the current time.

Next, we will create a table named "answers" to store the answers to the questions.

CREATE TABLE answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    answer_body TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
);

In the above code, we define the structure of the "answers" table. It contains the following columns:

• The "id" column is an auto-incrementing integer used as a unique identifier for each answer.
• The "question_id" column is an integer that represents the question to which the answer belongs. This column corresponds to the "id" column in the "questions" table and contains the foreign key constraint.
• The "answer_body" column is a text type used to store the specific content of the answer.
• The "created_at" column is a timestamp type used to record the creation time of the answer.
• The "modified_at" column is also a timestamp type, used to record the modification time of the answer. When updating answers, this column will automatically update to the current time.

In order to maintain the integrity and consistency of the data, we define the foreign key constraint of "question_id". This way, when a question is deleted, the corresponding answers will also be automatically deleted.

Now, we have successfully created the database table for online Q&A. You can use the following command to view the structure of these tables:

DESCRIBE questions;
DESCRIBE answers;

In practical applications, you can insert question and answer data into these tables and retrieve this data from the tables by writing SQL queries.

Through this simple example, we learned how to use MySQL to create an online question and answer table. Of course, this is just a starting point, and as your business needs grow, you may need to add more functions and tables to meet the needs of your users. Proficiency in MySQL table design and data operations will help you develop more stable and efficient applications.

To summarize, MySQL is a powerful relational database management system. It is very simple to use it to create an online question and answer table. Through reasonable design and optimization, we can create an efficient and reliable database structure to meet user needs. I hope this article will help you understand MySQL table design and the creation of online Q&A tables.

The above is the detailed content of MySQL table design tutorial to create a simple online question and answer table. For more information, please follow other related articles on the PHP Chinese website!