maple_shaft's questions

我现在正在努力处理以下 SQL 查询：

http://sqlfiddle.com/#!4/736a7/12

select uc.*
from usercredential uc
inner join users u on u.userid = uc.userid
inner join credential c on c.credentialid = uc.credentialid
inner join healthsystemcredential hsc on hsc.credentialid = c.credentialid
inner join accesslevel ac on hsc.hscredentialid in (
  -- Trying to figure out how to filter here?
  select 1 from dual
)
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null) or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate) )

基本上，我试图获得与凭据多对一的 UserCredential。凭证与 HealthSystemCredential 是一对多的。

HealthSystemCredential 具有 AccessLevel 的外键，但是 HealthSystemCredential 中的此 AccessLevel 列实际上是最低允许访问级别。AccessLevel 记录的类型等级介于最低的 1 和最高的 4 之间。

示例：当我按类型为 2、3 或 4 的 AccessLevels 过滤查询时，应获取具有 Type Rank 为 2 的 AccessLevel 记录的 HealthSystemCredential。

问题是我无法在一个查询中弄清楚如何做到这一点。有什么方法可以让我在 AccessLevels 的子查询中获取 DENSE_RANK 列，在子查询中应用我的过滤器并将其以某种方式加入到表中，以提供我想要的所有适用的 HealthSystemCredentials？

我需要为典型的 RDBMS 设计一个数据模型，以模仿典型体育联盟的结构。在架构上我的要求是：

• 良好归一化

• 事务性的

• 应用程序开发人员可以使用流行的 ORM 工具对其进行相当好的建模

所需模式的属性：

• 可以有很多联赛。

• 一个联盟中可能存在 1 个或多个分区。

• 一个部门中可能存在 1 个或多个会议。

• 一个会议中可能存在 1 个或多个团队。

• 当且仅当该分区未被划分为联盟时，一个分区中可能存在 1 个或多个团队（换句话说，一个分区不需要拆分为联盟，而是一个团队可以直接引用单个分区而不是会议）

• 一个团队中可能存在 1 个或多个玩家。

• Players存在于 1 个且仅 1 个Team中。

如果联盟结构是严格的，这将很容易做到并保持参考完整性。这就是我的想法，但我想知道这是否是一个好的设计，或者是否有更好的方法来应对这种情况。

• 联赛表：...

• LeagueGroup 表：FK - LeagueId NOT NULL，FK - LeagueGroupParentId NULLABLE，LeagueGroupType

• 球队：FK - LeagueGroupId NOT NULL

• 玩家：FK - TeamId NOT NULL

所以基本上我在想 Divisions 和 Conferences 将是同一张表，带有一个向上查找其分组父项的自引用外键。这样做的好处是联赛分组其实可以N深。

缺点是这在 ORM 框架中映射可能不是很容易或有用。此外，应用程序开发人员可能需要编写一些逻辑来将其构建到对他们的目的有用的树状对象结构中。更进一步，如果没有某种触发器，我不知道如何从数据库级别强制团队不能同时存在于分区和会议级别。

您对这种方法有何看法，您是否看到任何可行的替代方法？