I have a project that I am working on and I can't figure out which would be a "better" table relationship schema.
The scope of the area in question are:
-User uploads documents (become the owner/author)
-User can share document with other users (set share rights)
-Any user who has access to document can checkout the document (exclusive lock)
My original schema looks like this:
Benefits are:
-only one user can be author. (authorid)
-Rights table contain only "sharerights" (Read, Write)
-User has easy distinction as to which files they "own" (authorid) vs sharedfiles (sharedfiles table). (this one is a weak benefit, I know)
After thinking things through I thought that this might be a better schema:
Benefits are:
-All document associations are located in one location (UserFiles)
-Future ability to allow multiple authors/owner of a single document
Rights table would now have Read, Write, and Owner. As soon as a document was uploaded by a user, an automatic association would be made to a new document and the user would be given "owner" rights.
This led me to the final schema:
Benefits are:
-If a user file association is removed (removed share) and that user had a lock on the file (Checked out), then that exclusive lock would be automatically removed.
The only problem with this last model is that I plan to add "special" user for each department so that a user could share a document to an entire department. So I am not sure if I want to associate the share association to the checkoutID (if that makes sense). The query for the users files would look like "select all files where userfiles.userid = me.userid || (userfiles.id == SpecialDepID && me.depid == SpecialDepID)" (major pseudo code)
It has been a long time since I have done database schemas and this one design decision is really racking my brain. It is really bugging me as to which design would be "better" and by better I mean better design principles, better decision based off of previous experience, allows for easier "growth" in the design, etc. Please let me know your thoughts! Thanks!
FINAL SOLUTION
With the help from Michael Madsen the final solution looks like:
There will be a trigger on UserFiles for delete which will determine if a lock should be deleted when a relationship is removed.