5

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:

First appoach

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.

Second approach

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?

Improve this question
1
  • 2
    Databases are optimized for joins if you index correctly. In the second approach, you can't have multiple users per document.
    – HLGEM
    Commented Aug 16, 2012 at 20:47

2 Answers 2

Reset to default
3

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

Improve this answer
5
  • @Matt_M HasAccess is necessary, since a document may be related to a user without him having access to it. It could be a confidential document that an Admin user can see but not the user himself. Or it could be a document tailored for the user, but access has not been granted to him yet.
    – Jean-François Beauchamp
    Commented Aug 16, 2012 at 20:49
  • @Jean-François_Beauchamp That makes sense. In that case, I would leave HasAccess in the junction table.
    – Matt M
    Commented Aug 16, 2012 at 20:54
  • @Matt_M A while ago, I had noticed that some people put a surrogate PK in Many-to-Many relationship tables, ant that others don't, so I asked a DBA I know whether I should put one or not, and his answer was to put one, although I don't remember if he gave me any reason justifying his answer. So I always put one since then, but without understanding what the pros and cons are of doing so or not. Of course, if I put one, I also have to make the paire DocumentId, UserId a Unique Key.
    – Jean-François Beauchamp
    Commented Aug 16, 2012 at 20:57
  • @Matt_M Concerning the different level of access, there are two: ReadAccess, and NoReadAccess. A document can be related to a user without the user having access to the document.
    – Jean-François Beauchamp
    Commented Aug 16, 2012 at 23:28
  • @Jean-François_Beauchamp The surrogate PK issue is, and will continue to be, very divisive. It really boils down to storage efficiency, which is functionally equivalent to memory efficiency. Why add an additional key, when the combination of UserID and DocumentID both satisfies uniqueness AND is sufficiently narrow? I can see the argument for a surrogate key in this scenario, when you are going to subsequently join this junction table against something else enough to justify shaving off those 4 extra bytes per row.
    – Matt M
    Commented Aug 17, 2012 at 14:06
0

It seems to me as though the access property should be at the document level. Therefore, I would do the following. The constraint on UserId in Document could be NULL if it does not belong to a user.

User (UserId PK, Firstname, Lastname, Email, UserTypeId)
Document (DocumentId PK, UserId FK, Name, Path, AccessTypeId FK)
DocumentAccess (AccessTypeId PK)
Improve this answer
1
  • The thing is that if the document is generic, it may be shared with multiple users. In this case, I need a Many-to-Many relationship.
    – Jean-François Beauchamp
    Commented Aug 16, 2012 at 20:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.