求一个优雅的数据表设计思路

[moonfly]

求一个优雅的数据表设计思路

数据

mysql 数据库中主要存储 2 个 主要数据：IP 地址 、域名

数据关系

1. IP 地址 是根据新增的域名解析后存储的，因此会存在一个 域名 要对应多个IP 地址 的记录；
2. 同一个 IP 地址 也会存在同时被多个 域名 所解析的情况，因此也会存在一个 IP 地址 对应到多个 域名 的情况；

需要同时记录 域名、IP 地址 以及他们之间的对应关系；

业务需求

查询

1. 能快速查询到所有的 IP 地址 列表（不重复的）
2. 能快速查询出所有的 域名 列表 （不重复的）
3. 能根据指定 域名 快速查询出所对应的全部 IP 地址 列表
4. 能根据指定 IP 地址 快速查询出所对应的全部 域名 列表

更新

1. 新增一个 域名 时需要同步新增对应的 IP 地址 数据，
2. 删除一个 域名 时，需要删除它所对应的全部 IP 地址

请问最优雅的表结构该如何设计？

希望能保持 IP 地址 ,域名 字段都是唯一主键，以简化 1 ，2 的唯一查询的效率（避免额外的去重操作）；

但又必须要保存 多对多 的关联关系，以实现 3 ，4 的关联条件查询需求；

虽然大部分数据逻辑的问题是可以从业务代码的层面来覆盖处理的（例如将查询全部 list 后的去重操作放在代码里处理）；

但我还是想单纯从数据库的角度讨论看看，是否有最优的表设计方案，在满足以上需求的同时，最大化避免在代码中来处理部分的逻辑；

[wu00]

看你排版的这么优雅，表述的如此清晰，我有点好奇。
难道不是<域名表> <IP 表> <域名_IP 关系对照表> ？

[netnr]

新增两张表，单列主键，在维护时也保持同步操作
以空间换时间

[wanniwa]

一般就一楼的方案吧，需要关系就三表联查，有索引也很快，而且并不麻烦。

[37Y37]

一楼已经说了方案了，既然是域名相关的那我来分享下我们的域名系统，可以参考 https://blog.ops-coffee.cn/s/devops-domain-record-certificate

[kkwa56188]

这道是基础概念题.
ER = Entity + Relationship.
所以两个实体表, 一个关系表.

[pikko]

把你的问题复制到 gpt4 试了下，真的，以后我都没必要去发帖求助了

[huzhizhao]

不都是空间换时间，实体加关系？

[Hurriance]

我觉得一楼方案可以
能够理解你想把业务过程都后置到 DB 里的想法，但是业务过程如果是频繁改动的话，感觉放到应用里会更有利于后续的修改，DB 的 DML 修改成本会随着应用不断迭代而增加

[hez2010]

设计完之后还可以看看你的关系设计是否满足 3NF 范式。

不过 MySQL 引擎的索引做得依托答辩，想要最大化避免在代码中来处理部分的逻辑的话可能要用巨大的性能损失作为代价。建议只把 MySQL 当作支持关系型的 KV 来用，而不是当作关系型数据库来用。

[xuanbg]

用不着 3 张表，你 IP 表存什么？不就是一个 32 位整数？合理的数据结构就是：域名表(id, 域名)，域名-IP 表(id, 域名 id, ip)。

[hahaha777]

设计成：域名、解析类型、解析值？这样是不是更符合业务意义

[totoro52]

按照 SQL 规范去设计就好了，不想联表就代码逻辑处理， 别把问题想得太复杂 这就是一个多对多的关系

[Eissen]

在这个问题中，你需要处理的是一个典型的多对多关系。因此，你需要三个表：一个表用于存储唯一的 IP 地址，一个表用于存储唯一的域名，还有一个联接表用于存储它们之间的关系。这样可以满足你的需求，同时也可以优化查询性能。

以下是具体的建表语句：

IP 地址表 (ip_address)
<SQL>
CREATE TABLE ip_address (
id INT AUTO_INCREMENT PRIMARY KEY,
ip VARCHAR(15) NOT NULL,
UNIQUE (ip)
);
域名表 (domain)
<SQL>
CREATE TABLE domain (
id INT AUTO_INCREMENT PRIMARY KEY,
domain_name VARCHAR(255) NOT NULL,
UNIQUE (domain_name)
);
关联表 (domain_ip_address)
<SQL>
CREATE TABLE domain_ip_address (
domain_id INT,
ip_address_id INT,
PRIMARY KEY (domain_id, ip_address_id),
FOREIGN KEY (domain_id) REFERENCES domain(id),
FOREIGN KEY (ip_address_id) REFERENCES ip_address(id)
);
这种结构可以满足你的所有需求：

通过从 ip_address 或 domain 表中选择数据，可以快速查询所有的 IP 地址或域名（不重复的）。
根据指定的域名，可以通过联接 domain 、domain_ip_address 和 ip_address 表查询所对应的所有 IP 地址。
根据指定的 IP 地址，可以通过联接 ip_address 、domain_ip_address 和 domain 表查询所对应的所有域名。
更新操作（新增或删除域名）可以通过在 domain 表中插入或删除记录，然后更新 domain_ip_address 表来完成。
这种结构最大的优点是，它可以在数据库层面处理所有的逻辑，避免在代码中处理逻辑，从而提高代码的可读性和可维护性。null

[moonfly]

@wu00 对的，感谢精辟的总结，看了这么多专业的解答，应该您的答案就是最完美的了；
因为我不是专业的 DBA ，工作的习惯养成了喜欢一次尽可能详细清晰地描述问题，以避免来回多次的沟通成本；所以略显啰嗦了点

[moonfly]

@kkwa56188 感谢专业的解答，学到了新的概念知识

[moonfly]

@pikko
@Eissen
确实，不得不说善用 GTP 真的可以少掉好多头发 Orz 。。