类表继承与非规范化
我正在尝试对专业化/泛化进行建模，倾向于使用类表继承（请参阅此答案）。
然而，我的同事有维护和性能问题，因为同一个表会有许多（50+）重叠的专业化。他的建议是创建一个包含以下列的表：
- 引用通用表
- 引用维护专业化类型的表
- 引用维护属性值的表
这样所有属性都维护在一个表中，并且可以进行过滤通过专业专栏。我不知道这个设计叫什么，但我担心它与 EAV...
我主要关心的是修改异常，但除此之外我看不出有任何理由这是一个坏主意。一种解决方案是否明显优于另一种，或者我们应该选择一种并继续前进？
I'm trying to model a specialization/generalization, leaning towards using class table inheritance (see this answer).
However, my co-worker has maintenance and performance concerns because there will be many (50+) overlapping specializations of the same table. His suggestion is to create a table with the following columns:
- Reference to the general table
- Reference to a table that maintains types of specializations
- Reference to a table that maintains attribute values
That way all of the attributes are maintained in one table, and can be filtered by the specialization column. I don't know what this design is called, but I'm worried that it's somehow related to EAV...
My main concern is modification anomalies, but besides that I'm not seeing any reason it's a bad idea. Is one solution clearly superior to the other, or should we just pick one and move on?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在设计表格时，我通常会考虑一件事：用途。
我将如何在那里写入数据以及如何查询它。我将设计偏向于读取或写入，基于哪一个对性能更重要，重复性如何等。
您的问题并没有真正解释您的用法，因此我在这里回答中的所有建议都完全是猜测。如果每天插入 20k 行，设计将与每天插入 5 行不同。此外，如果您需要每天对任何类型的任何列运行 20k 次搜索，或者每天运行 5 次。这些会影响您设置桌子的方式。
话虽如此，一般方法可能是这样做：
50 多个重叠的专业化表将是编写查询的噩梦。我会尝试提出 1 个主要的通用表，也许还有 5 个左右的其他通用表，您可以在其中稍微了解一下“单表继承”（其中您可能有几列不适用于每种类型，但包含在内，以便您涵盖了尽可能多的类型的尽可能多的列）。从那里用类似 EAV 的方法覆盖剩余的列。
When designing tables I usually design them with one thing in mind: usage.
How will I write the data there and how will I query it back. I bias the design toward reads or writes based on which will be more important for performance, how repetitive, etc.
Your question does not really explain your usage, so all suggestions in my answer here are complete speculation. If you insert 20k rows a day the design would be different than if you insert 5 a day. Also if you need to run 20k searches per day on any column of any type or if you run 5 a day. these would affect how you set up your tables.
With that said, a general approach might be to do something like this:
The 50+ overlapping specializations tables will be a nightmare to write queries on. I would try to come up with 1 main general table and maybe 5 or so other general tables where you might dip a little into "Single Table Inheritance" (where you may have several columns that do not apply to each type, but are included so you cover as many columns from as many types as possible). From there cover the remaining columns with the EAV like approach.