Designing a reasonable MySQL database structure requires considering core modules such as user relationships, content releases, and interaction mechanisms. The user table should contain account, additional information and status fields; content release supports multimedia type expansion through the separation of posts and attachments; high-frequency interactive operations such as likes and comments need to establish a unique index and record time; focus on relationships to create a separate table to improve query efficiency; performance optimization can be achieved through cursor paging, cache hot data, partitioning by time, etc.

The data volume of social applications grows rapidly and has complex interactions. Designing a reasonable MySQL database structure is the key to ensuring performance and scalability. Many people start by building a few tables, but the more they make changes, they become more and more messy. In fact, from the beginning, we must consider how to organize core modules such as user relationships, content releases, and interaction mechanisms.

User table design: Don’t just save your account password

User information may seem simple, but in fact, a lot of additional data must be taken into account. For example, in addition to username, email address, and password, you also need to record the registration time, last login time, status (whether it is blocked), avatar link, etc.

 CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(255),
    status ENUM('active', 'suspended', 'deleted') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

suggestion:

• Use BIGINT instead of INT to avoid insufficient ID in the future.
• The password should not be stored in plain text, encrypted in bcrypt or other secure methods before saving.
• Record the update time field to facilitate troubleshooting or data analysis.

Content publishing structure: Post multimedia attachment

One of the core of social applications is the "posting" feature. Each post may contain attachments such as text, pictures, videos, etc. At this time, the multimedia information can be split into a table separately.

 CREATE TABLE posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE post_attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    url VARCHAR(255) NOT NULL,
    type ENUM('image', 'video', 'file'),
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

illustrate:

• Each post can have multiple attachments, associated through the post_attachments table.
• The attachment types are managed uniformly, which facilitates front-end display logic processing.
• If you need to support more media formats in the later stage, you can directly add a new value to type field.

User relationships and interactions: indexing should be considered for likes, comments, and followers

Social platforms interact frequently, such as likes, comments, forwarding, and following operations, they will frequently access the database. If the index is not done properly, the system can easily get stuck.

For example, the like table can be designed like this:

 CREATE TABLE likes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    post_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_post (user_id, post_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

Key points:

• Add a unique index to (user_id, post_id) to prevent repeated likes.
• The like record must also record the time to facilitate subsequent statistics on activity.
• The same idea can be used for comments, forwarding and other operations.

In addition, the attention relationship between users can also be created separately:

 CREATE TABLE follows (
    Follower_id BIGINT UNSIGNED NOT NULL,
    followed_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, followed_id),
    FOREIGN KEY (follower_id) REFERENCES users(id),
    FOREIGN KEY (followed_id) REFERENCES users(id)
);

This will enable you to quickly check who is following who, number of fans, followers, etc.

Performance optimization tips: paging, caching, partitioning

Although we are talking about the design of MySQL, we also need to consider some performance-level practices:

• Pagination query : Do not directly LIMIT offset, size , especially inefficient when large offsets. You can use "cursor paging" to turn pages based on timestamps or IDs.
• Cache hot data : For example, the number of likes and comments of popular posts can be cached in Redis to reduce database pressure.
• Partition by time : Partition by time for tables with more logs or tables with a lot of historical data to improve query efficiency.

For example, a simple pagination method:

 SELECT * FROM posts WHERE created_at < &#39;2024-01-01&#39; ORDER BY created_at DESC LIMIT 10;

In this way, it is much faster to continue to check according to the time of the last page of the previous page.

Basically that's it. The data model of social applications does not seem complicated, but to truly support high concurrency and multiple interactions, every detail must be thought of in advance.

The above is the detailed content of Designing MySQL Databases for Social Media Applications. For more information, please follow other related articles on the PHP Chinese website!