Problems involving classes and subclasses (or, if you prefer, types and subtypes) come up all the time in database design. Your situation looks like a case in point.
In your case, here's what I would do, using a technique called Class Table Inheritance combined with a technique called Shared Primary key.
Create three tables: Contacts, Professors, and Students. Use the autonumber feature for the ID field of the Contacts table. Have an ID field in the other two tables, but do not use the autonumber feature. Instead, whenever you insert a new Contact, obtain the ID field just generated for the new contact, and provide that ID field as the value for ID in either Professors or Students. The ID field is declared as the primary key in all three tables. You can even create a Contact that is both a student and a professor, if that makes sense in your situation.
You place other attributes in the appropriate table, according to the attribute, as you have suggested.
ID is guaranteed to be unique in all three tables. If you want data about only students, join Contacts and Students matching on the ID field. Likewise for data about professors.
Using shared primary key in this way enforces the 1:1 nature of the relationships between Students or Professors and Contacts.
Good luck!
A slight variation on Rolandos answer. Rather than a TEXT column, you could use an INT column as a mask value. Using a bitwise OR to test the mask would give you way to determine if the field is shown.
select id,
case when 1 | mask = mask then col1 end col1,
case when 2 | mask = mask then col2 end col2,
case when 4 | mask = mask then col3 end col3,
case when 8 | mask = mask then col4 end col4,
...
from table
So if you want to show columns 1 and 3 then the mask would be 5.
Editing the mask is just a matter of adding and subtracting the appropriate values.
For example if you want to make column 3 public for a user
update table
set mask = mask + 4
where id = 1
and 4 | mask <> mask -- Make sure it isn't already set
Edit By @RolandoMySQLDBA
Perhaps to apply IF function to your idea
SELECT
IF(POWER(2,0) | mask = mask,fld1,'Private') fld1,
IF(POWER(2,1) | mask = mask,fld2,'Private') fld2,
IF(POWER(2,2) | mask = mask,fld3,'Private') fld3,
IF(POWER(2,3) | mask = mask,fld4,'Private') fld4,
...
IF(POWER(2,n) | mask = mask,fldn,'Private') fldn
FROM user_table;
Maybe, this might work too
SET @xp = -1;
SELECT
IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld1,'Private') fld1,
IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld2,'Private') fld2,
IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld3,'Private') fld3,
IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld4,'Private') fld4,
...
IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fldn,'Private') fldn
FROM user_table;
so you don't have to hardcode the powers of 2,
This would only work for n <= 32. Don't know what would happen if there would be 33+ columns.
Best Answer
The first method is cleaner. I would make a few suggestions, however. You don't need the UserDocumentID surrogate Primary Key. A composite key of UserID and DocumentID would suffice, while also provide a unique constraint for the junction table. Also, is HasAccess really necessary? I believe the existence of the record in the junction table is enough to indiciate that access is granted.
Do you have different levels of access? Read-only, read/write, delete?
Hope this helps,
Matt