三个相关实体的表格设计

Question

场景如下：

       n   ownership    1
stocks <-------------------- users

      n    belong to         n
users -----------------------> sectors 

       n    having            1
stocks <---------------------- sectors

我为他们设计的是使用6个表：T_Stocks，T_Users，T_Sectors，T_UserStocks，T_UserSectors，T_SectorStocks，但我不认为这是一个好的设计。如果您对此有任何想法，请帮助与我分享。

Answer 1

你是对的，这不是一个好的设计。 （抱歉！:P）由于每个 stock 只有一个 user 作为其所有者，因此您只需在 中添加一个 ownerId 列即可股票表。给它一个外键约束，并记住在上面放一个索引！同样，每个sector 只有一个user，因此您也可以在sectors 中拥有一个userId 列。 （但这对我来说似乎是倒退的；您是否打算以相反的方式建立这种关系？）

但是，此时将 sectorId 放入 stocks 会破坏规范化，因为你可以创建一只股票，其用户与其所在行业的用户不同。如果你不关心标准化，你可以祈祷永远不要把事情搞砸。或者，由于后两个关系的组合意味着第一个关系，因此您可以放弃 stocks 上的 userId 列，而只拥有 sectorId 。

Answer 2

如果用户只能拥有他们参与的行业中的股票，那么也许行业与用户的交集（n:m）并不是真正必要的。用户是否可以在某个行业但不拥有该行业的股票？如果用户严格凭借拥有该行业的股票而处于该行业，那么您的模型会简单得多。像这样：

SECTOR
  sector_id
, name_of_sector
, ... (anything else about sectors)

STOCK
  stock_id
, name_of_stock
, ... (whatever that pertains to the stock as a whole)
, sector_id     -- What sector is this stock in?

STOCK_CERTIFICATE
  stock_certificate_id
, stock_id        -- Which stock is it?
, quantity        -- number of shares, etc.
, user_id  NULL   -- who owns this stock, could be nobody?

USER
  user_id
, user_name
, ... (anything else about the user)

现在，如果您想知道谁拥有多少股票，您只需查看 STOCK_CERTIFICATE 表即可。如果您想知道用户位于哪些部门，您可以使用如下查询：

SELECT DISTINCT
  A.sector_id
, A.name_of_sector
FROM SECTOR A 
  INNER JOIN STOCK B 
    ON A.sector_id = B.sector_id
  INNER JOIN STOCK_CERTIFICATE C
    ON B.stock_id = C.stock_certificate_id
WHERE
  C.user_id = {whatever your user ID is}

如果您想知道哪些用户位于给定部门，您还可以执行类似的反方向选择。

编辑：用户的会计在他们所属的部门中具有角色...

如果用户通过在部门中扮演角色来参与部门（根据@OP的评论），那么您需要在用户和部门之间有一个交集表，如下所示如下...

USER_ROLE_IN_SECTOR
  user_id    (PK, FK)
, sector_id  (PK, FK)
, role_id    (PK, might be FK if you also have a ROLE table...)

这假定用户可以在任何给定部门中拥有一个或多个角色。如果用户在任何给定扇区中最多只能拥有一个角色，则必须对 user_id +扇区_id 包含唯一约束。如果只有一个用户可以在任何给定扇区中拥有特定角色，那么您需要对扇区_id + role_id 进行唯一约束。

这个新表处理了这样一个事实：用户可以参与行业，而无需实际拥有给定行业的任何股票。上面最初提出的其他表格仍然是记录谁拥有哪些股票以及哪些股票属于哪些板块的最佳方式。该模型采用第三范式，因此即使 user_id 和扇区_id 在 USER_ROLE_IN_SECTOR 表中重复，本身没有冗余。

如果您想强加一条业务规则，规定用户不能拥有某个行业的股票，除非他们在该行业中具有某种角色，那么您将需要一些其他类型的约束。没有干净的方法可以用普通的引用完整性来执行这种类型的约束。这是一种需要程序风格检查而不是引用约束的事情。