用户向实体添加自定义属性的最佳方法是什么？
今天再次提出如何处理允许用户将自定义字段添加到产品数据库中提供的标准实体模式。我赞成实际上提供一个函数，为用户提供有限的 DDL 函数，这样他们实际上可以向表中添加新的自定义字段。另一种方法是为自定义字段（例如 Customers 和 CustomersEx）建立一个单独的表，其中只有 CustomersEx 可以更改，但这里的更新比平常更棘手。我们讨论的最后一个也是最糟糕的选项是提供一个 EAV 表，其中行是实体名称、字段名称、字段值。
哪种方法最好？
It came up again today how to handle allowing users to add custom fields to the standard entity schemas delivered in your product's database. I favour actually providing a function that gives the user limited DDL functions, so they can actually add a new, custom field to a table. Another approach is to have a separate table for custom fields, e.g. Customers, and CustomersEx, where only CustomersEx can change, but here updates become trickier than normal. The last and most badass option that we discussed was providing a EAV table, where rows are entity name, field name, field value.
Which approach is best?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
添加到现有关系结构的 EAV。 SQL Server CAT（客户顾问团队）发布了关于此主题的白皮书。尽管是特定于供应商的，但讨论的原则和建议的解决方案适用于大多数 RDBMS：性能和可扩展性语义数据建模的最佳实践
EAV that is added to the existing relational structure. There is a whitepaer published by SQL Server CAT (Customer Advisor Team) on this topic. Although is vendor specific, the principels discussed and the solutions proposed apply to most RDBMS: Best Practices for Semantic Data Modeling for Performance and Scalability
我赞成你的第二个想法。我们做了类似的事情，但将表称为 Attr[ibute] 表，例如 Company、CompanyAttr。属性是我们业务对象 (1:M) 中的集合，NHibernate 处理数据库操作。我们在 UI 中将这些字段显式显示为属性，并且不会尝试将它们显示为表中的附加字段。
I favor your second idea. We do something similar but call the tables Attr[ibute] tables, e.g. Company, CompanyAttr. The attributes are a collection in our business object (1:M) and NHibernate handles database operations. We explicitly display these fields as attributes in the UI and don't try to display them as if they were additional fields in the table.
我喜欢通过 DDL 将字段添加到表中，但该表应该与主表分开。这样，您就可以编写对数据库架构的更改的脚本，而不会影响用户的自定义字段添加。右连接很容易完成，如果没有自定义字段，您将不需要单独表中的记录。
如果您只想以垂直方式显示数据，EAV 表可能是一个不错的选择。您还可以运行数据透视查询来水平显示它们。
I favor adding fields to a table via DDL, but that table should be separate from the main table. That way, you can script changes to your database schema without affecting your users' custom field additions. A right-join is easy enough to accomplish, and you won't need the record in the separate table if there are no custom fields.
If you just want to display data in a vertical fashion, EAV tables can be a good choice. You can also run a pivot query to display them horizontally.