views:

39

answers:

1

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:

alt text

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:

alt text

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:

alt text

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:

alt text

There will be a trigger on UserFiles for delete which will determine if a lock should be deleted when a relationship is removed.

+1  A: 

If it were only those three options, I'd go with the second one and place a trigger on deletion from UserFiles to handle the issue you're trying to handle with the third design.

You've already provided good reasons for choosing that one over the first one, so I'm not going to repeat that.

The third design isn't good, though - it's not straightforward to see if a file is locked; you have to see if a sharedFileID exists where the fileID matches the one you're after, meaning multiple records per table. It's also not nice that you're missing a primary key on CheckedOutFiles, so that also counts against that one.

However, we can of course fix those problems. If you were to use FileID as the primary key in CheckedOutFiles, you would be able to avoid those two issues - you have a meaningful primary key, and you can easily check if a given file is locked.

Of course, even if you do that, you still have the problem of the "special" user. A simple way you could use to handle that is by storing the actual user as part of your checkout table - the sharedFileID references the department user, while you still have a reference to the actual user to verify that you're dealing with the right user.

With those changes, the third design seems best - you only reserve space for lock information for the files that are actually locked.

TL;DR: Third design, but with fileID as the PK in CheckedOutFiles, and a specific UserID as part of CheckedOutFiles to handle "meta"-users.

Michael Madsen
1. for CheckedOutFiles, would I set the fileID as the primary key of that table AND set it as a foreign key of the Files table? Can a column be both a primary key and a foreign Key? 2. what does TL;DR mean? 3. So the CheckedOutFiles table would look like fileID (PK/FK), userID (FK), and also a sharedFileID (FK)? or without the sharedFileID? Also, would sharedFileID always be filled (aka allow nulls?) You response makes _alot_ of sense and is helping alot thanks!
BabelFish
@Babelfish: 1. Yes and yes. 2. TL;DR is short for "Too long; didn't read", indicating that a summary of the post follows for those who think it's too long to read in its entirety. 3. If you want to be able to revoke the lock along with the privilege, then yes, you would also have sharedFileID, and it would always point to the record that grants the permission to the user in question - no NULLs. If the permission record is deleted, then cascading will remove the lock as well.
Michael Madsen
That doesn't, however, handle the scenario where multiple records can grant a single user access to olck the file - e.g. a record explicitly for that user, and one for a meta-user which matches him as well. If you want to handle that scenario, you would need a DELETE trigger on the permission table to check if the lock should be removed.
Michael Madsen
Also note that this all assumes that your meta-user permissions end up being stored in the same table as the regular user permissions - if you use two different tables, then you would have to allow NULLs and have a second FK in CheckedOutFiles which points to the other permission table.
Michael Madsen
I am not sure if I followed that. Are you talking about if a user had access to a file because it was shared to him both directly and indirectly through a department share? If UserFiles had an entry to a special user (aka a department) then the rights specified on that entry would dictate what rights the department had for that file. A User is granted whatever the max rights are for a file if he had multiple accesses to that file (directly shared to him and also indirectly shared through the department)
BabelFish
If I am adding a delete trigger then I could remove the sharedFileID from the CheckedOutFiles table. That table would then contain fileID (PK/FK), and userID (FK). The Delete trigger would happen on UserFiles and would determine if the userID still had proper access to the fileID if something was deleted that involved a file in question. Would that work?
BabelFish
@BabelFish: Yes, that's the situation I was thinking of. If both of those entries allow him to lock a file, then it's not really correct to revoke the lock íf just one of those entries are removed. To handle that, then you would need a delete trigger exactly like the one you describe.
Michael Madsen
@Michael would that also mean that I could remove the sharedFileID from the CheckedOutFiles as well? What purpose is that relationship providing for me? Also, does a delete trigger "break" separation of concerns of my datatier from my business logic layer?
BabelFish
@Michael or would I keep that association and the delete trigger would verify if another connection existed (in which case the sharedFileID would be changed to the other sharedFileID relationship), else the CheckedOutFile entry would simply be removed.
BabelFish
@BabelFish: You don't need sharedFileID if you have a trigger that validates if the lock should be released, because you'll be looking for a unique record anyway (the fileID itself). You *can* place the check in the BLL instead, but IMO, this seems to be a matter of data consistency - by using a trigger, you can guarantee that you don't have any hanging locks even if direct database access is used. If you believe that to be more of a business logic thing, then you can implement it in your BLL, but if it's about data consistency, then keeping it in the DB makes more sense to me.
Michael Madsen
perfect thanks! this has really helped get my mind going in the right direction.
BabelFish
@BabelFish: Of course, there may be other ways to handle this - for example, if you have a requirement that departments can only be granted read permission, and therefore cannot lock a file, then there's no need for a trigger, and you can place sharedFileID as a FK, and use the relationship between the two tables to use a cascade delete. It all depends on the exact requirements you have.
Michael Madsen
@Michael yeah, departments may be granted write permission
BabelFish