mysql - 黑名单/白名单表设计

Question

我们有一组用户

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 的域列表。可以在单个查询中访问，理想情况下执行相反操作(列出给定域可以访问的所有数据)

到目前为止，我们的方法是一张 table

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也相对较小(低于 10 万行)

编辑:问题更多地围绕语义和最佳实践。有了上面的结构，我相信我们可以让它工作，但是模式“感觉不对”，我想知道是否有更好的方法

Answer 1

有两个问题需要考虑，规范化和管理。
要以传统方式标准化，您需要 4 个表。
设置3个主表USER、DOMAIN、OtherDATA。
使用 User_Id、Domain_Id、OtherDATA_Id、PermissionLevel 设置子表
这提供了最少数量的重复数据。它还使用户域级别的管理更容易。您还可以在用户和域表中添加默认的白名单/黑名单字段。通过这种方式，脚本可以自动填充子表，然后经理可以进入并调整所需的一个值。
如果您有两个不同的表，一个用于白名单，一个用于黑名单，您可能会意外地在两个列表中同时获得一个用户或域。实际上它将是 4 个表，2 个用于用户，2 个用于域。管理会更复杂。