正确的表架构
我正在开发一个项目，但我无法弄清楚哪个是“更好”的表关系模式。
相关区域的范围为：
用户上传文档（成为所有者/作者）
用户可以与其他用户共享文档（设置共享权限） )
任何有权访问文档的用户都可以签出该文档（独占锁）
我的原始架构如下所示：
优点是：
只有一个用户可以成为作者。 (authorid)
权限表仅包含“共享权限”（读、写）
用户可以轻松区分他们“拥有”哪些文件（ authorid）与共享文件（共享文件表）。 （我知道，这是一项微弱的好处）
经过深思熟虑，我认为这可能是一个更好的模式：
优点是：
所有文档关联都位于一个位置 (UserFiles)
未来能够允许单个文档的多个作者/所有者
p>
现在权限表将具有读、写和所有者。一旦用户上传文档，就会自动关联到新文档，并且用户将被授予“所有者”权限。
这让我得出了最终的架构：
好处是：
- 如果用户文件关联被删除（删除共享）并且用户对该文件有锁定（签出），那么该独占锁定将自动删除。
最后一个模型的唯一问题是我计划为每个部门添加“特殊”用户，以便用户可以与整个部门共享文档。所以我不确定是否要将共享关联与 checkoutID 相关联（如果有意义的话）。对用户文件的查询看起来像“选择所有文件，其中 userfiles.userid = me.userid || (userfiles.id == SpecialDepID && me.depid == SpecialDepID)”（主要伪代码
）自从我完成数据库模式以来已经很长时间了，这个设计决策真的让我绞尽脑汁。哪个设计会“更好”真的让我很困扰，我所说的更好是指更好的设计原则，根据以前的经验做出更好的决策，允许设计更容易“成长”等等。请让我知道你的想法！
最终解决方案
在 Michael Madsen 的帮助下，最终解决方案如下所示：
UserFiles 上将有一个触发器用于删除将确定在删除关系时是否应删除锁。
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!
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果只有这三个选项，我会选择第二个选项，并在从 UserFiles 中删除时放置一个触发器，以处理您尝试使用第三个设计处理的问题。
您已经提供了选择第一个而不是第一个的充分理由，因此我不会重复这一点。
不过，第三种设计并不好——要查看文件是否被锁定并不容易；您必须查看是否存在共享文件ID，其中文件ID 与您要查找的文件ID 匹配，这意味着每个表有多个记录。如果您在 CheckedOutFiles 上缺少主键，这也不太好，因此这也会对主键产生影响。
然而，我们当然可以解决这些问题。如果您使用 FileID 作为 CheckedOutFiles 中的主键，您将能够避免这两个问题 - 您拥有一个有意义的主键，并且您可以轻松检查给定文件是否被锁定。
当然，即使你这样做，你仍然会遇到“特殊”用户的问题。您可以用来处理此问题的一个简单方法是将实际用户存储为结帐表的一部分 - sharedFileID 引用部门用户，同时您仍然拥有对实际用户的引用来验证您是否正在处理正确的用户。
通过这些更改，第三种设计似乎是最好的 - 您只需为实际锁定的文件的锁定信息保留空间。
TL;DR：第三种设计，但使用 fileID 作为 CheckedOutFiles 中的 PK，并使用特定的 UserID 作为 CheckedOutFiles 的一部分来处理“元”用户。
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.