【Mark】2-自建邮箱-MySql数据库设计

外星人群控

对上一篇发的数据库结构做了优化，加了索引和说明

1. 用户表（users）

字段名、字段类型和中文注释

id: INT - 用户ID，主键
username: VARCHAR(255) - 用户名
password_hash: VARCHAR(255) - 密码哈希
email: VARCHAR(255) - 邮箱地址，唯一
created_at: TIMESTAMP - 创建时间
updated_at: TIMESTAMP - 更新时间

索引类型和原因

username：普通索引（INDEX），加速基于用户名的cha询，例如用户登录或搜索用户信息。
email：唯一索引（UNIQUE INDEX），确保邮箱地址的唯一性，并加速基于邮箱的cha询。

2. 域名表（domains）

字段名、字段类型和中文注释

id: INT - 域名ID，主键
domain_name: VARCHAR(255) - 域名，唯一
created_at: TIMESTAMP - 创建时间
updated_at: TIMESTAMP - 更新时间

#### 索引类型和原因
domain_name：唯一索引（UNIQUE INDEX），确保域名的唯一性，并加速域名的cha询。

3. 用户域名关联表（user_domains）

字段名、字段类型和中文注释

id: INT - 关联ID，主键
user_id: INT - 用户ID，外键
domain_id: INT - 域名ID，外键
created_at: TIMESTAMP - 创建时间
updated_at: TIMESTAMP - 更新时间

索引类型和原因

user_id：普通索引（INDEX），加速基于用户ID的cha询，例如查找特定用户的域名关联信息。
domain_id：普通索引（INDEX），加速基于域名ID的cha询，例如查找特定域名关联的用户

。

4. 邮件表（emails）

字段名、字段类型和中文注释

id: INT - 邮件ID，主键
sender_id: INT - 发件人ID，外键
recipient_id: INT - 收件人ID，外键
subject: VARCHAR(255) - 邮件主题
body: TEXT - 邮件内容
sent_at: TIMESTAMP - 发送时间
received_at: TIMESTAMP - 接收时间
domain_id: INT - 域名ID，外键

索引类型和原因

sender_id：普通索引（INDEX），加速基于发件人ID的cha询，例如cha询某个用户发送的邮件。
recipient_id：普通索引（INDEX），加速基于收件人ID的cha询，例如cha询某个用户接收的邮件。
received_at：普通索引（INDEX），加速基于邮件接收时间的cha询，例如cha询某个时间段内的邮件。
domain_id：普通索引（INDEX），加速基于域名ID的cha询，例如cha询特定域名下的邮件。

5. 附件表（attachments）

字段名、字段类型和中文注释

id: INT - 附件ID，主键
email_id: INT - 邮件ID，外键
file_path: VARCHAR(255) - 文件路径
file_name: VARCHAR(255) - 文件名
created_at: TIMESTAMP - 创建时间
updated_at: TIMESTAMP - 更新时间

索引类型和原因

email_id：普通索引（INDEX），加速基于邮件ID的cha询，例如cha询特定邮件的附件。

引用表关系和设计思路

引用表关系

1. 用户表（users）
-  用于存储用户的基本信息。

2. 域名表（domains）
-  用于存储多个域名的信息。

3. 用户域名关联表（user_domains）
-  用户表和域名表之间的关联表，用于管理用户与域名之间的关系。

4. 邮件表（emails）
-  用于存储邮件的信息，包括发件人、收件人、主题、内容和时间戳等。

5. 附件表（attachments）
-  用于存储邮件的附件信息。

设计思路

1. 用户管理
-  用户表（users）用于存储用户的基本信息，包括用户名、密码哈希、邮箱地址等。
-  通过用户名和邮箱地址的索引，加速用户登录和搜索操作。

2. 域名管理
-  域名表（domains）用于存储邮箱系统支持的多个域名。
-  通过唯一索引确保每个域名的唯一性，并加速域名的cha询。

3. 用户与域名的关联
-  用户域名关联表（user_domains）用于管理用户与域名之间的关系。
-  一个用户可以关联多个域名，一个域名也可以被多个用户关联。
-  通过用户ID和域名ID的索引，加速cha询用户的域名关联信息。

4. 邮件存储
-  邮件表（emails）用于存储邮件的信息，包括发件人、收件人、邮件主题、邮件内容、发送时间和接收时间等。
-  通过发件人ID、收件人ID和接收时间的索引，加速cha询用户发送和接收的邮件。
-  通过域名ID的索引，加速cha询特定域名下的邮件。

5. 附件管理
-  附件表（attachments）用于存储邮件的附件信息。
-  每个附件记录与邮件表（emails）中的一条记录关联。
-  通过邮件ID的索引，加速cha询特定邮件的附件。

创建表的SQL语句

[SQL] 纯文本查看 复制代码

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 用户ID，主键
    username VARCHAR(255), -- 用户名
    password_hash VARCHAR(255), -- 密码哈希
    email VARCHAR(255) UNIQUE, -- 邮箱地址，唯一
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 更新时间
    INDEX (username), -- 普通索引，加速基于用户名的cha询
    INDEX (email) -- 唯一索引，确保邮箱地址的唯一性
);

-- 创建域名表
CREATE TABLE domains (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 域名ID，主键
    domain_name VARCHAR(255) UNIQUE, -- 域名，唯一
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 更新时间
    INDEX (domain_name) -- 唯一索引，确保域名的唯一性
);

-- 创建用户域名关联表
CREATE TABLE user_domains (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 关联ID，主键
    user_id INT, -- 用户ID，外键
    domain_id INT, -- 域名ID，外键
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 更新时间
    FOREIGN KEY (user_id) REFERENCES users(id), -- 外键，引用users表的id
    FOREIGN KEY (domain_id) REFERENCES domains(id), -- 外键，引用domains表的id
    INDEX (user_id), -- 普通索引，加速基于用户ID的cha询
    INDEX (domain_id) -- 普通索引，加速基于域名ID的cha询
);

-- 创建邮件表
CREATE TABLE emails (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 邮件ID，主键
    sender_id INT, -- 发件人ID，外键
    recipient_id INT, -- 收件人ID，外键
    subject VARCHAR(255), -- 邮件主题
    body TEXT, -- 邮件内容
    sent_at TIMESTAMP, -- 发送时间
    received_at TIMESTAMP, -- 接收时间
    domain_id INT, -- 域名ID，外键
    FOREIGN KEY (sender_id) REFERENCES users(id), -- 外键，引用users表的id
    FOREIGN KEY (recipient_id) REFERENCES users(id), -- 外键，引用users表的id
    FOREIGN KEY (domain_id) REFERENCES domains(id), -- 外键，引用domains表的id
    INDEX (sender_id), -- 普通索引，加速基于发件人ID的cha询
    INDEX (recipient_id), -- 普通索引，加速基于收件人ID的cha询
    INDEX (received_at), -- 普通索引，加速基于邮件接收时间的cha询
    INDEX (domain_id) -- 普通索引，加速基于域名ID的cha询
);

-- 创建附件表
CREATE TABLE attachments (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 附件ID，主键
    email_id INT, -- 邮件ID，外键
    file_path VARCHAR(255), -- 文件路径
    file_name VARCHAR(255), -- 文件名
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 更新时间
    FOREIGN KEY (email_id) REFERENCES emails(id), -- 外键，引用emails表的id
    INDEX (email_id) -- 普通索引，加速基于邮件ID的cha询
);