I am modelling a database for an application I am developing and I am in front of a choice for which I would like to know what the best practice is.
Let's say I have a Documents table and a Users Table. Documents can be generic or related to a user in three ways:
Created by the user, in which case he has access to the document; About the user, in which case access to the document may or may not have been granted to him; Tailored for that user, in which case access to the document may or may not have been granted to him yet;
I could model a Many-to-Many relationship with a table containing a DocumentId field, a UserId field, and a bit telling whether the user has access to the document:
Another approach would be to have a Many-to-Many table with a UserId field and a DocumentId field to link Documents to Users that have access to them, and to also have a UserId field in the Documents table that is Null if the document is generic or that contains the ID of the user it is related to.
The first approach looks tidier because there is only one UserId FK. The second approach however allows to filter Documents by user without the need to join a table.
What is the best practice here?