title: Blacklist / Whitelist Table Design
body: <p>We have a set of users</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Each user can have one or many domains, such as</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And we have a table that has some sort of data, for this example it doesn't really matter what it contains</p>
  
  <pre><code>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
  </code></pre>
  
  <p>We want certain elements of <code>some_data</code> to be accessible to only certain <code>users</code> or only certain <code>domains</code> (whitelist case).
  In other cases we want elements of <code>some_data</code> to be accessible to everyone BUT certain <code>users</code> or certain <code>domains</code> (blacklist case).
  Ideally we would like to retrieve the list of domains that the given element of <code>some_data</code> is accessible to in a single query and ideally do the reverse (list all the data the given domain has access to)</p>
  
  <p>Our approach so far is a single table</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The problem however is the fact that we need to query the db twice (to figure out if the given data entry is operating a blacklist or a whitelist [whitelist has higher priority]). (EDIT: it can be done in a single query)
  Also since the domain_id is nullable (to allow blacklisting / whitelisting an entire user) joining is not easy</p>
  
  <p>The API that will use this schema is currently hit 4-5k times per second so performance matters.
  The <code>users</code> table is relatively small (50k+ rows) and the <code>domains</code> table is about 1.5 million entries. <code>some_data</code> is also relatively small (sub 100k rows)</p>
  
  <p>EDIT: the question is more around semantics and best practices. With the above structure I'm confident we can make it work, but the schema "feels wrong" and I'm wondering if there is better way</p>