MySQL (InnoDB) 和排除关联

Question

我有三个表(people、clients_people、clients)，将使用这些表进行连接

SELECT * FROM people
LEFT JOIN clients_people ON people.id = clients_people.person_id
LEFT JOIN clients ON clients_people.client_id = client.id

每个客户端记录都有一个位置 - L1、L2 和 L3(client.location = 'L1' 等)。我希望能够存储每个人的排除位置；例如我想定义 people.id=1 以在新查询中排除clients.location = 'L1' 和clients.location = 'L3'。

我现在有两个解决方案。

1. 将列 client_location_exclude 添加到 people 表中，并将“L1,L3”存储在 people.id=1 记录中。
2. 允许将负整数存储在定义排除项的clients_people.client_id中；例如clients_people.client_id = -1 定义 L1 排除，clients_people.client_id = -2 定义 L2 排除，依此类推。

这两个解决方案都是好的(优点/缺点)吗？有更好(最好)的解决方案吗？

数据将用于 Ruby on Rails 应用程序和 PHP(无框架)应用程序；但从我的角度来看，我更感兴趣的是如何构建表格来保存并轻松利用位置排除。

谢谢。

Answer 1

正常的形式是使用..表(!)..来存储关系。毕竟它是一个关系数据库系统。如果您有一个位置表，能够将引用完整性添加到排除列表中，这也是有意义的。存储 'L1,L3' 不利于标准化，而且很难在后续工作中使用(您始终需要字符串拆分函数来访问各个元素)。

表格看起来像

people_location_exclusions: people_id, location_id

查询可能如下所示

   SELECT *
     FROM people
LEFT JOIN clients_people
       ON people.id = clients_people.person_id
LEFT JOIN clients
       ON clients_people.client_id = client.id
LEFT JOIN people_location_exclusions
       on people_location_exclusions.people_id = people.id
      and people_location_exclusions.location_id = clients.location_id
    WHERE people_location_exclusions.people_id is NULL

即使客户是通过客户人员链接的，LEFT JOIN + WHERE 子句也会从结果中删除客户