黑白名单表设计
我们有一组用户
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(254) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED每个用户可以有一个或多个域，例如
CREATE TABLE `domains` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` varchar(11) NOT NULL,
`domain` varchar(254) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `domain` (`domain`),
CONSTRAINT `domains_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED我们有一个表，其中包含某种类型的数据，对于本例来说，它包含的内容并不重要
CREATE TABLE `some_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`content` TEXT NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED我们希望some_data的某些元素只对特定的users或特定的domains (白名单情况)可访问。在其他情况下，我们希望每个人都可以访问some_data的元素，除了某些users或某些domains (黑名单情况)。理想情况下，我们希望在单个查询中检索some_data的给定元素可以访问的域的列表，理想情况下可以执行相反的操作(列出给定域可以访问的所有数据)
到目前为止，我们的方法是单个表
CREATE TABLE `access_rules` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rule_type` enum('blacklist','whitelist')
`some_data_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`domain_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `access_rules_some_data_id_fk` FOREIGN KEY (`some_data_id`) REFERENCES `some_data` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED然而，问题是我们需要查询数据库两次(以确定给定的数据条目是操作黑名单还是白名单具有更高的优先级)。(编辑:可以在单个查询中完成)此外，由于domain_id可以为空(以允许将整个用户列入黑名单/白名单)，因此加入并不容易
将使用此模式的API目前每秒被访问4-5k次，因此性能很重要。users表相对较小(50k+行)，domains表大约有150万个条目。some_data也相对较小(小于100k行)
编辑:问题更多地围绕语义和最佳实践。有了上面的结构，我相信我们可以让它工作，但是模式“感觉不对”，我想知道是否有更好的方法
回答 1
Stack Overflow用户
发布于 2020-10-15 00:47:04
有两个问题需要考虑，规范化和管理。要在传统上进行标准化，您需要4个表。
设置3个主表USER、DOMAIN、OtherDATA。
使用User_Id、Domain_Id、OtherDATA_Id、PermissionLevel设置子表
这提供了最少的重复数据量。它还使用户域级别的管理变得更容易。您还可以在用户和域表中添加默认的白名单/黑名单字段。这样，脚本可以自动填充子表，然后管理器可以直接进入并调整所需的值。
如果您有两个不同的表，一个用于白名单，一个用于黑名单，那么您可能会意外地在两个列表上都获得一个用户或域。实际上是4个表，2个用于用户，2个用于域。管理将会更加复杂。
https://stackoverflow.com/questions/57163084
复制相似问题
腾讯云开发者
