Timeline for Database modelling best practice question
Current License: CC BY-SA 3.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 17, 2012 at 14:06 | comment | added | Matt M | @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. | |
| Aug 17, 2012 at 0:42 | vote | accept | Jean-François Beauchamp | ||
| Aug 16, 2012 at 23:28 | comment | added | Jean-François Beauchamp | @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. | |
| Aug 16, 2012 at 20:57 | comment | added | Jean-François Beauchamp | @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. | |
| Aug 16, 2012 at 20:54 | comment | added | Matt M | @Jean-François_Beauchamp That makes sense. In that case, I would leave HasAccess in the junction table. | |
| Aug 16, 2012 at 20:49 | comment | added | Jean-François Beauchamp | @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. | |
| Aug 16, 2012 at 20:32 | history | answered | Matt M | CC BY-SA 3.0 |