数据库设计:借用资产融资

Question

在我的数据库中，我有三张关于不同类型资产的表格：house and kind 、locative buildings和stocks。

另一张表格是关于借用的。

现在我不得不把资产和借款联系起来。规则是：-一笔借款可以选择只与一项资产挂钩。-一项资产可选择与一项或多项借款挂钩。

我从数据库设计开始：

CREATE TABLE CUSTOMER
(
  CUSTOMER_ID INT NOT NULL,
  [...] 

  CONSTRAINT PK_CUS_ID PRIMARY KEY (CUS_ID)
)

CREATE TABLE ASSET1
(
  ASSET1_ID   INT NOT NULL,
  CUSTOMER_ID INT NOT NULL,
  [...]

  CONSTRAINT PK_ASSET1_ID PRIMARY KEY (ASSET1_ID),  
  CONSTRAINT FK_ASSET1_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE ASSET2
(
  ASSET2_ID   INT NOT NULL,
  CUSTOMER_ID INT NOT NULL,
  [...]

  CONSTRAINT PK_ASSET2_ID PRIMARY KEY (ASSET2_ID),  
  CONSTRAINT FK_ASSET2_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE ASSET3
(
  ASSET3_ID   INT NOT NULL,
  CUSTOMER_ID INT NOT NULL,
  [...]

  CONSTRAINT PK_ASSET3_ID PRIMARY KEY (ASSET3_ID),  
  CONSTRAINT FK_ASSET3_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE BORROWING
(
  BORROWING_ID INT NOT NULL,
  CUSTOMER_ID  INT NOT NULL,
  ASSET1_ID    INT     NULL,
  ASSET2_ID    INT     NULL,
  ASSET3_ID    INT     NULL,
  [...]

  CONSTRAINT PK_BORROWING_ID PRIMARY KEY (BORROWING_ID),

  CONSTRAINT FK_BORROWING_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_BORROWING_ASSET1   FOREIGN KEY (ASSET1_ID  ) REFERENCES ASSET1   (ASSET1_ID  ) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_BORROWING_ASSET2   FOREIGN KEY (ASSET2_ID  ) REFERENCES ASSET2   (ASSET2_ID  ) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_BORROWING_ASSET3   FOREIGN KEY (ASSET3_ID  ) REFERENCES ASSET3   (ASSET3_ID  ) ON DELETE NO ACTION ON UPDATE NO ACTION,

  CONSTRAINT CHK_BORROWING CHECK 
  (
      ((ASSET1_ID IS     NULL) AND (ASSET2_ID IS     NULL) AND (ASSET3_ID IS     NULL))
   OR ((ASSET1_ID IS NOT NULL) AND (ASSET2_ID IS     NULL) AND (ASSET3_ID IS     NULL))
   OR ((ASSET1_ID IS     NULL) AND (ASSET2_ID IS NOT NULL) AND (ASSET3_ID IS     NULL))
   OR ((ASSET1_ID IS     NULL) AND (ASSET2_ID IS     NULL) AND (ASSET3_ID IS NOT NULL))
  )
)

这是工作和参考的完整性似乎是好的。

但我已经知道，我将不得不在我的数据库中添加更多的资产，CHK_BORROWING将成为一个怪物。

我的问题是:有没有更好、更干净、更简单的解决方案？

谢谢

Answer 1

正如Walter所建议的，解决方案是类表继承。

如果您使用类表继承，您可以创建一个表，让我们称之为ASSETS_GENERIC。每个资产，无论类型如何，都将在此表中有一个条目。现在，您可以用通常的方式(参考资料)将借款和Assets_generic联系起来。现在，您可以在ASSETS_GENERIC和每个ASSETx表之间建立一个连接。