具有许多布尔“状态”的表领域
我有一个表，它代表一个具有多个“状态”的对象（已批准、已过期、趋势、真实）。所有这些状态都是独立的，所以我不能有一个单一的状态字段，因为可以进行查询，这样你就可以问：
select * from Objects WHERE approved is true, trending is true, expired is false;
我不确定是否有办法规范化这样的表。另外，据我了解，对这些字段使用索引基本上是没有用的。这也适用于本案吗？
谢谢！
I have a table which represent an object that has multiple "status" (approved, expired, trending, real). All these status are independent, so i can't have like a single status field because queries can be made so you can ask:
select * from Objects WHERE approved is true, trending is true, expired is false;
I'm not sure if there is anyway to normalize such table. Also, from what i understand, using indexes for those fields would be basically useless. Is that right for this case also?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
反对大量布尔状态列的争论并不是由于空间甚至规范化本身。如果每列完全与键相关并且不包含值的混合，则它已足够标准化（第三正常）。相反，问题在于列的布尔性质及其缺乏扩展性。例如，您向我们展示了一个名为
approved的列。批准日期和批准人是谁？如果批准管道扩展到包括更多状态而不仅仅是批准/未批准，例如已启动、待定、已批准和已拒绝，会发生什么？审批状态的描述如何？今天可能是Y/N，但明天您的客户可能想要批准/拒绝或者Qui/Non。我并不是建议你彻底改变你的设计。布尔列有其用武之地。但是，应在仔细考虑扩展性和可扩展性后使用它们。
The argument against lots of boolean status columns isn't due to space or even normalization per se. If each column relates entirely to the key and does not consist of an amalgam of values, it is normalized enough (3rd normal). Rather, the issue is with the boolean nature of the columns and their lack of expansion. For example, you showed us a column called
approved. What about the date it was approved and by whom? What happens if the approval pipeline expands to include more states than just approved/not approved such asinitiated,pending,approvedandrejected? What about the description for the approval status? Today it mightY/Nbut tomorrow your customers might wantApproved/Rejectedor perhapsQui/Non.I'm not suggesting you turn your design on its head; boolean columns have their place. However, they should be used after careful consideration about expansion and extensibility.