SQL Server DB & App设计级建议需要。数据库或应用程序中的约束？

Question

我需要一些实现业务规则的建议--无论是在DB中(使用触发器)还是在应用程序代码中。

--- Table structure:---

# ORG - Master table for Organizations 
# USER - Master table for Users (each user belongs to an Org so there's a field OrgId which is FK ro ORG) 
# SITE - Master table for Sites

# ORGSITE - {OrgId, SiteId} links Site(s) with Org(s) 
# USERSITE - {UserId, SiteId} links Site(s) with User(s)

约束是：“只有当用户的组织可以访问网站时，网站才能被用户访问。”

现在，应用程序中发生了这样的情况:在day1上，我们将Site1与Org1联系起来，然后我们能够将Site1与User1联系起来(User1属于Org1)。在day2上，我从ORGSITE中删除Site1和Org1之间的关系(这要求我也从USERSITE表中删除相应的User1 & Site1关系)。

这是在应用程序代码中处理的。所以，现在我的问题是，我在哪里保持上述约束处理-

APPROACH#1:

在ORGSITE表和USER表上部署触发器，它们将处理以下活动：

1. 关于ORGSITE的后删除(删除相应的USERSITE记录)
2. 用户更新后(如果用户的Org被更改，那么从USERSITE中删除他的所有记录)

APPROACH#2:

处理代码中的所有内容--点击触发这些DB操作的事件，并删除USERSITE中的记录(在必要时)。需要通过事务进行管理。

APPROACH#3:

简单地，在USERSITE表中添加一个新的字段OrgSiteId，这是一个FK到ORGSITE的“自动增量PK: Id”。接下来，我将为USERSITE.OrgSiteId FK部署级联删除。这将处理大部分的事情，并使它隐含！

希望我解释得很好。APPROACH#3真的要工作吗？如果不是--你的喜好是什么?为什么？

谢谢您抽时间见我。

Answer 1

如果您认为有人会直接从DB运行查询(比如DBA)，那么您应该在触发器中处理它。如果你通过应用程序做，你必须始终通过应用程序。

Answer 2

我建议：

• 删除UserSite表
• 创建一个名为UserSite的视图 从用户a中选择a.UserId，b.SiteID，在b.orgid=A.Org上左加入b.OrgSite

这将返回用户可以看到的站点，如果用户看不到任何站点，则返回NULL。

Answer 3

这些器官是传宗接代的吗？有ORG.parent_org_id字段吗？

如果是这样的话，您的问题就会更加困难，因为您可能需要一个人才能看到所有与他们的组织或其组织的子站点相关联的站点(有点像文件系统中的文件夹权限)。

如果数据库支持递归联接或公共表表达式，上述Sparky视图解决方案在这种情况下仍然可以工作。ANSI-标准的方法是使用CTE(我认为只有SQL 2005及更高版本和PostgreSQL支持)。对于相同的递归功能，Oracle和其他DB具有非标准语法。