数据库设计问题（额外字段与额外表）
我正在设计一个（关系）数据库模式，并想询问以下哪一个是最合适的设计。
场景：
表 ProductProperties：60 个字段（通过字段 CreatedBy 外键引用用户表）
表 Users：5 个字段
该应用程序还允许用户创建基本和高级过滤器。这些过滤器与 ProductProperties 表中的属性相同。基本过滤器使用 10 个字段，而高级过滤器则由全部 60 个字段组成。
现在我可以：
1) 在表 ProductProperties 中添加三个字段，即 FilterName、IsAdvancedFilter、IsFilter（导致大量空值）对于实际产品但不是过滤器的记录）
或者，
2）创建一个过滤器表，该表几乎是 ProductProperties 表的复制品，从而产生两个非常相似的大表，
哪一个设计更好？
谢谢，
I am designing a (relational) database schema and wanted to inquire which of the following would be the most appropriate design.
Scenario:
Table ProductProperties: 60 fields (foreign key reference to Users table via field CreatedBy)
Table Users : 5 fields
The application also allows users to create basic and advanced filters. These filters are the same as the properties in ProductProperties table. Basic filter uses 10 fields, while an advanced filter is composed of all 60.
Now I can either:
1) Add three fields to Table ProductProperties namely, FilterName, IsAdvancedFilter, IsFilter (resulting in lots of null values for records which are actual products but not filters)
Or,
2) Create a Filters table which would be a near replica of the ProductProperties table resulting in two big very similar tables
Which would be the better design ?
Thanks,
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
哪一个是更好的设计？
嗯，不是“设计”，而是两个建议的选择。绝对 (2)
就设计而言，具有 60 个字段、空值且非常大的 ProductProperties 并未标准化。因此，如果您想要设计或数据库，要做的第一件事就是标准化野兽。
然后你就会有设计。还有几个更小的表，它们更容易处理，并且允许更多的关系功能。然后，当您添加过滤器时，您将根据需要添加一些较小的表，而不是将三列添加到单个笨重的表中，或制作其副本。
其次，与上述分开，我不明白如何或为什么（2）过滤器表将是产品属性的近乎复制品，您能解释一下吗？
Which would be the better design ?
Well, not "design", but choice of two proposals. Definitely (2)
As far as design is concerned, ProductProperties with 60 fields, null values, and very large, is not normalised. So the first thing to do if you want design or a database, is normalise the beast.
Then you will have design. And several smaller tables, which are easier to deal with, and allow more relational capability. Then when you add filters, you will be adding a few smaller tables, as appropriate, not adding three columns to a single unwieldy table, or making a copy of it.
Second, and separate to the above, I do not understand how or why (2) the Filter table, would be a near replica of Product Properties, can you please explain.
更好的设计总是在不同的表中有不同的“东西”。如果过滤器确实与产品不同，那么将它们混合在一起会在尝试处理它们时造成许多麻烦。将它们分开可以消除这些令人头痛的问题。
至于公共列：表不是类，如果其中一些具有公共列也没关系。
最后的想法。当您将一列添加到一列时，您必须将其添加到另一列。与仅仅因为不同的事物具有共同的值而将不同的事物混合到同一个表中时所带来的头痛相比，这是微不足道的。
The better design is always to have different "things" in different tables. If a filter is truly different from a product, mixing them together will create many headaches when trying to deal with them. Keeping them separate eliminates those headaches.
As for the common columns: tables are not classes, it's ok if some of them have columns in common.
Final thought. When you add a column to one, you will have to add it to another. This is trivial compared to the headaches you get when unlike things are mixed into the same table just because they have values in common.