SQL - how to keep track of "simple relations"

Question

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

• users table has a uid primary key and user info
• notifications table has a nid primary key and notification text
• notifications_seen table with two columns, uid and nid

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?

Answer 1

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.

Answer 2

You could make an index on notifications_seen that contains both columns! Or create a separate column just for a primary key, or do both - having an index on uid and nid might 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.

Answer 3

You may be able to create a compound primary key (consisting of both uid and nid).