数据库设计-布尔属性或新表 [英] Database design - boolean attribute or new table

问题描述

假设我有一个要基于布尔属性（例如 flagged属性）进行过滤的数据库表。只是在表中添加一个已标记属性，还是制作一个对基表具有外键的新表更好？优点和缺点是什么？

Let's say I have a database table that I want to filter based on a boolean attribute (for example, "flagged" attribute). Is it better to just add a "flagged" attribute to the table, or make a new table which has foreign key to the base table? What are the pros and cons?

推荐答案

如果这是 all ，则需要 ¹ ，然后只需添加简单字段即可。

If that is all you need¹, then just add the simple field.

但是，您必须谨慎对待它的索引方式。除非这些值严重偏斜 ² ，否则您最终将获得具有可怕的选择性和群集因子 ³ ，实际上，进行全表扫描 ⁴ 会更好。

But, you'll have to be careful about how you index it. Unless the values are seriously skewed², you'll end-up with an index with a horrible selectivity and clustering factor³, and you'll actually be better off doing full table scans⁴.

如果与其他字段结合使用标志 进行过滤，请建立一个复合索引，这样可以更好地获得体面的

If you filter by the flag in conjunction with other fields, make a composite index, which will then have a much better chance of being decently selective.

¹ 即您不需要以某种方式描述或扩充两个可能的布尔值中的每一个的其他数据。

¹ I.e. you don't need additional data that somehow "describes" or "augments" each of the two possible Boolean values.

² 一个值是极少数的（包含 false 的行数比包含 true ，反之亦然），您碰巧只过滤该值。

² One value is in deep minority (the number of rows containing false is much smaller than the number of rows containing true, or vice verse), and you happen to filter on just that value.

³ 链接适用于Oracle，但是一般原则适用于所有DBMS。

³ The link is for Oracle, but the general principle applies to all DBMSes.

⁴ 即使在存在索引的情况下也自动为您服务。假设的愚蠢 DBMS只会盲目地使用索引，并且其性能甚至不如全表扫描。

⁴ Which a decent DBMS will do for you automatically, even in the presence of the index. A hypothetical "stupid" DBMS would just blindly use the index and perform even worse than the full table scan.

这篇关于数据库设计-布尔属性或新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！