构建“笔记”的正确方法多个数据集的表？
请原谅标题，我很难在没有过度描述的情况下表达我的问题。
我的应用程序有这样的表：
联系人
属性
事件
我正在添加一种将注释附加到上表中的项目的方法。我希望单个注释可以与联系人、财产或事件（或三者的组合）相关联。
目前我的笔记表如下所示：
noteID int
note创建数据时间
注意内容文字
userID int（创建注释的用户 ID）
contactID int
属性ID int
eventID int
有问题的部分以粗体显示。现在，当我为事件创建注释时，我只需插入注释并设置eventID。如果事件也与联系人相关，我也可以添加contactID（将设置contactID 和eventID）。虽然它有效，但我认为它效率低下并且没有正确规范化。
我想做的是创建一对多关系，问题是“多”部分可以有不同的目标表。同时，我希望减少选择或插入注释所需的查询数量。
我的想法是创建一个将它们连接在一起的表，然后为属性、联系人和事件赋予其自己的保持不变的唯一目标类型。但我仍然觉得这不是最好的方法。或者，我可以为每个目标表（notes_properties、notes_contacts 等）创建一个单独的关系表。
noteID int
目标ID int
目标类型 int
如有更多帮助，我们将不胜感激。谢谢 ：）
Please excuse the title, I had trouble wording my question without being overly descriptive.
My application has tables likes these:
contacts
properties
events
I am adding a method of attaching notes to the items in the above tables. I would like it so that a single note can associate with a contact, property or event (or a combination of the three).
Currently my notes table looks like this:
noteID int
noteCreated datatime
noteContent text
userID int (userid that created the note)
contactID int
propertyID int
eventID int
The portion in question is in bold. Right now, when I create a note for an event, I simply insert the note and also set the eventID. If the event also relates to a contact, I can add the contactID as well (contactID and eventID would be set). While it works, I think it is inefficient and not properly normalized.
What I am trying to do is create a one-to-many relationship, problem is that the "many" part can have different target tables. At the same time I want to reduce the number of queries necessary to select or insert a note.
My thought was to create a table that connects them together, then give properties, contacts, and events their own unique targetType that stays constant. But I still feel that is not the best way to do it. Alternatively I can create a separate relationship table for each target table (notes_properties, notes_contacts, etc...).
noteID int
targetID int
targetType int
Much help would be appreciated. Thank you :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果笔记和联系人、属性和联系人之间的关系事件是 1 比 1，例如，一个联系人只能有一个注释，那么对其进行建模的最佳方法就是简单地将
noteID列添加到联系人、属性和事件表中。如果它是多对多关系，例如，一个联系人可以有许多注释，那么您需要创建一个单独的表，例如具有两列的
contact_notes-contactID和noteID。不要纠结于一个注释可以与多个实体关联的事实。它实际上根本不会影响您建模的方式。
If the relationship between notes and contacts, properties & events is 1 to 1, e.g., a contact can have only one note, then the best way to model it would be to simply add a
noteIDcolumn to the contact, properties and events table.If it's a many-to-many relationship, e.g., a contact can have many notes, then you would want to create a separate table, say
contact_noteswith two columns -contactIDandnoteID.Don't get hung up on the fact that a note can be associated to multiple entities. It really doesn't affect the way you model it at all.
你所描述的...
...是经典多对多关系的一种变体（额外假设联系人、财产或事件可以有多个注释，以及处理四个表而不是两个表的额外复杂性）。
正确规范化这种情况的方法是使用一个中间表，其中包含您在问题中描述的字段：
正如您所指出的，这将使某些查询更难编写并且效率更低。具体来说，外连接（您几乎肯定需要它，因为我确信注释是可选的）将必须使用子查询。
我建议稍微反规范化并使用如下所示的中间表：
What you're describing...
...is a variation of a classic many-to-many relationship (with the additional assumption that a contact, property or event can have more than one note and the additional complication of dealing with four tables instead of two).
The way to properly normalize that situation is with an intermediate table with the fields as you've described in your question:
As you point out though, this will make certain queries harder to write and more inefficient. Specifically, outer joins (which you'll almost certainly need since I'm sure notes are optional) will have to use subqueries.
I'd suggest denormalizing slightly and going with an intermediate table that looks like this:
如果您确实想将所有注释保留在一个表中，我会推荐这样的结构：
那么您可以使用以下方式加入：
或
if you really want to keep all the notes in a single table I would recommend a structure like this:
then you can join using:
or