相似结构的理想数据库模式
在我们的业务应用程序中，我们需要存储用户或系统生成的有关特定实体的“评论”。例如，可以创建有关客户、订单或采购订单的评论。除了引用的实体之外，这些评论都具有许多相同的特征。
所有评论都需要日期、时间、用户和评论文本。它们还需要引用表的外键，以便您可以查找该特定实体的注释。
目前，该应用程序对于每种类型的评论（例如 customer_comments、order_comments、purchaseOrder_comments）都有一个单独的表。这些表的模式在日期、时间、用户和评论文本方面都是相同的，但每个表都有一个与评论所属的相应表的 FK。
这是最好的设计还是有更好的设计？
In our business application, we have a need to store user or system generated "comments" about a particular entity. For example, comments can be created about a customer, an order, or a purchase order. These comments all share many of the same characteristics, with the exception of the referenced entity.
All comments require the date, time, user, and comment text. They also require a foreign key to the referenced table so that you can look up comments for that particular entity.
Currently the application has a separate table for each type of comment (e.g. customer_comments, order_comments, purchaseOrder_comments). The schemas of these tables are all identical with respect to the date, time, user and comment text, but each has a FK to the respective table that the comment is for.
Is this the best design or is there a better design?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
就我个人而言，我会创建一个评论表，然后为每个实体（客户、订单等）使用一个交集表将评论与实体链接起来。
Personally, I'd create a single comment table and then use one intersection table per entity (customer, order, etc.) to link the comment with the entity.
您可以将所有这些评论放入一个表
comments中，并添加另一列commented_table，这将使其成为多态 ManyToOne。一些框架/orms（如 Rails 的 ActiveRecord）确实内置了对此的支持，如果您使用的任何东西没有内置支持，那么基本上就像添加另一个where子句一样简单you could put all these comments into one table
commentsand add another columncommented_table, which would make it a polymorphic ManyToOne. some frameworks / orms (like Rails' ActiveRecord) do have built in support for that, if whatever you're using doesn't, it's basically as simple as adding anotherwhereclause如果是我，我想我会有一个
Comments表，其中有一个额外的comment_type_id字段，该字段映射到评论是否应可供customer< /code> 实体、order实体等...在其他地方，您需要有一个comment_type_id引用的comment_type表。这样，如果您决定向评论添加额外的元数据，则只需在单个
Comments表中进行操作，而不是在customer_comments中进行，order_comments等...If it had been me, I think I would have had a single
Commentstable with an extracomment_type_idfield that maps to whether the comment should be available forcustomerentities,orderentities, etc... Somewhere else you'd need to have acomment_typetable thatcomment_type_idrefers to.This way, if you decide to add an extra piece of meta-data to comments, you only have to do it in a single
Commentstable rather than incustomer_comments,order_comments, etc...