Home >Database >Mysql Tutorial >MySQL Table Design Guide: Creating a Simple Email Subscription Table
MySQL Table Design Guide: Creating a Simple Email Subscription Table
Introduction:
In modern society, the email subscription function has become one of the important features of many websites. Through email subscription, the website can send users the latest news, event notifications, product promotions, etc. In order to achieve this function, we can use MySQL to create a simple but practical email subscription form. This article explains how to design an email subscription form and provides corresponding code examples.
Table design:
First, we need to create a table to store the user's subscription information. The design of the table should consider the following aspects:
CREATE TABLE Subscription ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100), status ENUM('active', 'inactive') DEFAULT 'active' );
CREATE TABLE Category ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE SubscriptionCategory ( subscription_id INT, category_id INT, FOREIGN KEY (subscription_id) REFERENCES Subscription(id), FOREIGN KEY (category_id) REFERENCES Category(id), PRIMARY KEY (subscription_id, category_id) );
Code example:
Add a subscription:
INSERT INTO Subscription (name, email) VALUES ('John', 'john@example.com');
Unsubscribe:
UPDATE Subscription SET status = 'inactive' WHERE id = 1;
Add topic category:
INSERT INTO Category (name) VALUES ('News'); INSERT INTO Category (name) VALUES ('Events');
Specify subscriber topic category:
INSERT INTO SubscriptionCategory (subscription_id, category_id) VALUES (1, 1); INSERT INTO SubscriptionCategory (subscription_id, category_id) VALUES (1, 2);
Query subscriber information and the subject categories they subscribe to:
SELECT s.name, s.email, c.name FROM Subscription s INNER JOIN SubscriptionCategory sc ON s.id = sc.subscription_id INNER JOIN Category c ON sc.category_id = c.id;
Summary:
Through the introduction of this article, we learn Learn how to use MySQL to create a simple email subscription form. Reasonable table design can improve data storage efficiency and query efficiency, making the system more stable and reliable. At the same time, by providing code examples, we can clearly understand how to operate this subscription form. I hope this article can help readers better understand and apply the MySQL database.
The above is the detailed content of MySQL Table Design Guide: Creating a Simple Email Subscription Table. For more information, please follow other related articles on the PHP Chinese website!