SQL - 如何跟踪“简单关系”
我希望有人可以编辑我的标题以更好地描述我的意思，因为我不知道这到底是什么。但是，请考虑以下设置：我想创建一个通知系统，向用户显示一条消息，直到他单击“关闭”。然后我需要“记住”该用户已取消通知，因此我不会再次向他显示该通知。这是我当前的解决方案
users表有一个uid主键，用户信息notifications表有一个nid主键和通知文本notifications_seen表，包含两列，uid和nid
当有人点击通知上的“关闭”时，我会存储他们的 uid 和通知的 nid 在 notifications_seen 中。这似乎工作正常，但 phpMyAdmin 有巨大的红色消息告诉我 notifications_seen 没有索引。然而，这两列都不是唯一的。我真的应该在 notifications_seen 中添加一个完全无用的列并将其称为主键吗？有更好的方法吗？
I hope somebody can edit my title to better describe what I mean, because I don't know exactly what this would be called. However, consider this setup: I want to create a notification system, where a message is displayed to a user until he clicks "dismiss". I then need to "remember" that this user has dismissed the notification so I don't show it to him again. Here is my current solution
userstable has auidprimary key and user infonotificationstable has anidprimary key and notification textnotifications_seentable with two columns,uidandnid
When somebody clicks dismiss on a notification, I store their uid and the notification's nid in notifications_seen. This seems to work fine, but phpMyAdmin has giant red messages telling me that notifications_seen does not have an index. However, neither column is unique. Should I really have an extra utterly useless column in notifications_seen and call that a primary key? Is there a better way to do this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以使用多个列来创建主键。在这种情况下，您应该将 nid 和 uid 设置为 notification_seen 表中的主键。这里的想法是，即使 nid 或 uid 在你的 notification_seen 表中都不是唯一的； nid/uid 对是唯一的。您应该向这两列添加主键约束。这通常是您在这种情况下想要做的事情。
有时您可能实际上想要创建自动增量行来简化主键。例如，当你的最佳候选键由很多列组成时（我是凭空想象出来的；但假设有 4 列或更多列）或者你有包含字符串的列；进行查找时匹配速度会较慢。但对于这种情况，只需向两列添加主键约束就足够了。
主键默认被索引；这就是为什么您应该将主键约束添加到两列。这还可以确保您不会意外插入具有相同 uid/nid 对的行，从而保持数据的完整性。
您还应该将 uid 上的外键约束添加到 users 表中的 id 上，并将外键约束添加到 notification 表中 id 上的 nid 上。添加外键约束将确保您不会将实际不存在的 uids 或 nid 插入到 notification_seen 表中。
You can use more than one column to create your primary key. In this case, you should set nid AND uid as your primary key in your notifications_seen table. The idea here is that even though neither nid or uid will be unique within your notifications_seen table; the nid/uid PAIR is unique. You should add a primary key constraint to these two columns. This is usually what you would like to do for this kind of situation.
There are times where you might actually want to create an auto-increment row to simplify the primary key. For example, when your best candidate key consists of a lot of columns (I'm pulling this out of the air; but lets say 4 or more columns) or you have columns which contain strings; which would be slower to match when doing lookups. But for this situation, just adding the primary key constraint to the two columns should be more than fine.
Primary keys are indexed BY default; which is why you should just add the primary key constraint to the two columns. This will also preserve the integrity of your data by making sure you don't accidentally insert rows with the same uid/nid pair.
You should also add a foreign key constraint on the uid to the id in the users table, and a foreign key constraint to the nid on the id in the notifications table. Adding the foreign key constraints will ensure you don't insert uids or nids which don't actually exist into your notifications_seen table.
您可以创建复合主键（由
uid和nid组成）。You may be able to create a compound primary key (consisting of both
uidandnid).您可以在
notifications_seen上创建一个包含两列的索引！或者仅为主键创建一个单独的列，或者同时执行这两种操作 - 在uid和nid上建立索引可能会加快查询速度（但不会在您开始注意到主要的性能问题之前，不要太担心这一点 - 只要记住它以供将来使用）。拥有这些 n:n 关系的主键并不是一件可怕的事情。You could make an index on
notifications_seenthat contains both columns! Or create a separate column just for a primary key, or do both - having an index onuidandnidmight speed up queries (but don't worry too much about that until you start to notice major performance problems - just remember it for the future). Having a primary key for these n:n relations isn't a terrible thing.