I've developed a system for assigning classes to a document. We have a class table which simply has an id and a name for the class. The Name field must be unique. I set this up in creating the table using PHPMyAdmin.
Now I tie documents to a class and users to a class with a userClass table and a document class table. These are simple tables. The documentClasses table has the primary key id plus documentID and classID columns. The userClasses table has the same structure with a primary key id, userID and classID column.
Here's the problem, to prevent duplicates from showing up in the list I have to find a way to prevent duplicate pairs of data. If I assign Unique to classID then can not assign multiple documents the same class. If I make the documentID unique I can not assign multiple classes to a document.
What I want to prevent is a document or user being assigned to the same class twice. IOW the DocumentClasses table cannot look like this:
If anyone has an idea of how to prevent 1, 3 from showing up twice I'd appreciate a suggestion. I have no clue where to start.