Postgresql

為資金轉移業務開發數據庫，其中 (a) 個人和組織可以 (b) 發送和接收資金

  • February 14, 2019

在相關的業務環境中，成員和組織都需要有一個資金**賬戶。可以轉移**資金

  • 從會員到會員，
  • 從成員到組織，
  • 從一個組織到另一個組織，以及
  • 從組織到成員。

注意事項

為了為這樣的場景建構數據庫，我創建了以下三個表：

CREATE TABLE Members ( 
 memberid serial primary key, 
 name varchar(50) unique, 
 passwd varchar(32), 
 account integer 
);

CREATE TABLE Organizations (  
 organizationid serial primary key, 
 name varchar(150) unique, 
 administrator integer references Members(memberid), 
 account integer 
);

CREATE TABLE TransferHistory  
 "from" integer, -- foreign key?
 "to" integer, -- foreign key?
 quantity integer 
);

我認為該TransferHistory表有必要顯示誰/什麼向誰/什麼發送了資金。

問題是，既然Members和Organizations是不同的表，我如何從TransferHistory表中引用它們？

例如，涉及的數據可以顯示如下：

Account      Account      Quantity
-----------  -----------  --------
1072561733  38574637847       500
38574637847   1072561733       281

這表明帳戶需要記錄在同一個表中，但帳戶是針對兩種不同類型的所有者（成員和組織）的，每個所有者都保留在各自的表中。

我可以創建一個名為 的表Accounts，所以現在我將有四個表：

CREATE TABLE Members (
 memberid serial primary key, 
 name varchar(50) unique, 
 passwd varchar(32), 
 accountid integer references Accounts(accountid) 
);

CREATE TABLE Organizations (
 organizationid serial primary key, 
 name varchar(150) unique, 
 administrator integer references Members(memberid), 
 accountid integer references Accounts(accountid)
);

CREATE TABLE Accounts ( 
 accountid serial primary key, 
 state integer 
);

CREATE TABLE TransferHistory ( 
 "from" integer references Accounts(accountid), 
 "to" integer references Accounts(accountid), 
 quantity integer 
);

…但現在我必須確保Members和Organizations表中的每個外鍵不指向表中的同一Account行Accounts…

…或者我可以有一個Accounts具有兩個外鍵的表，一個指向Members另一個指向Organizations（並且一個外鍵列必須始終包含一個 NULL 標記）。但是現在關於查詢的事情變得有點混亂。一般來說，數據庫設計如下：

CREATE TABLE Members ( 
 memberid serial primary key, 
 name varchar(50) unique, 
 passwd varchar(32) 
);

CREATE TABLE Organizations (
 organizationid serial primary key, 
 name varchar(150) unique, 
 administrator integer references Members(memberid) 
);

CREATE TABLE Accounts ( 
 accountid serial primary key, 
 member integer references Members(memberid), 
 organization integer references Organizations(organizationid),
 state integer 
);

CREATE TABLE TransferHistory ( 
 "from" integer references Accounts(accountid), 
 "to" integer references Accounts(accountid), 
 quantity integer 
);

那麼，有人對如何解決這個問題有建議嗎？

如果打算建構一個關係數據庫，那麼首先執行 (a) 對感興趣的業務上下文的分析——為了描繪概念模式——在實體類型方面，檢查它們的屬性和關聯之前是非常有幫助的(b) 考慮表格、列和約束——與邏輯層相對應的方面—— 。按照這個過程，準確地捕捉業務領域的含義，然後將其反映在一個實際的、受到良好約束的 SQL-DDL 設計中會容易得多。

關係範式提供的眾多優點之一是它允許以自然結構管理數據。因此，在使用關係工具來管理它之前，必須“找到”這種結構。所討論的場景是否與個人項目有關（正如您通過評論指出的那樣）並不重要：您定義的越現實，您將從它的開發中學到的東西越多（如果這是這項工作的目的）。當然，一個現實的個人項目可能會演變成一個具有相對較小適應能力的商業項目。

商業規則

為了呈現您可能希望用作參考的第一個進展，我制定了一些概念級別的業務規則，它們是最重要的規則之一，列舉如下：

  • 一個人擁有零個或多個帳戶
  • 一個人主要通過它的ID來區分
  • 一個人由他/她的Firstname、Lastname、BirthDate和Gender交替區分
  • 一個組織擁有零一個或多個帳戶
  • 組織主要通過其ID進行區分
  • 組織由其名稱交替區分
  • 組織在成立日期開始運營
  • 賬戶是零一或多轉賬中的轉讓**方
  • 賬戶是零一或多轉賬的**受讓人
  • 帳戶主要由其編號**標識
  • 帳戶在確切的CreatedDate發行
  • 在轉讓中，轉讓人必須與受讓人不同
  • 一個人可以通過零或一的使用者配置文件登錄

由於（1） Person和Account之間以及（2）Organization和Account之間的關聯（或關係）非常相似，因此這一事實表明Person和Account是Party的實體子類型（基本上是個人或一組個人） ，這又是他們的實體超類型。這是一個經典的資訊結構，在不同種類的多個概念模型中非常頻繁地出現。以這種方式，可以斷言兩個新規則：

  • PartyType對零個或多個締約方**進行分類
  • 一方是個人或組織_

之前的兩個業務規則可以合併為一個：

  • 一方擁有零一個或多個帳戶

這也可以從Account實體類型的角度來表述：

  • 帳戶由唯一一方擁有

說明性IDEF1X圖

因此，我創建了一個說明性（簡化）IDEF1X **†**圖，它綜合了上面製定的規則，如圖 1所示：

圖 1 - 資金轉移 IDEF1X 模型

黨、人、組織：超型-亞型結構

如圖所示，Person和Organization被描述為 的互斥子類型Party。

超Party類型包含一個判別器（即PartyTypeCode）和它的子類型共有的所有屬性（或屬性），而這些子類型又具有適用於每個子類型的屬性。

帳戶

實體類型與Account直接連接，它提供了 (i)和之間以及 (ii)和之間Party的後續連接。Account``Person``Account``Organization

因為在現實世界中，(a) 銀行Account是不可轉讓的，即它Owner不能改變，並且 (b) 沒有，銀行Account不能開始成為​​目前或啟用的Owner，所以這種實體類型的主鍵可能包括屬性PartyId 和 AccountNumber，因此您應該更徹底地分析場景以高精度地定義該點。

轉移

另一方面，Transfer實體類型呈現由三個屬性組成的複合主鍵，即，TransferorAccountNumber（TransfereeAccountNumber我分配的角色名稱以區分每個實例中涉及的兩個Account屬性中的每一個）和（它告訴確切的即時事件發生時間執行）。Transfer``TransferDateTime``Transfer

關於帳號的因素

還要注意，在實際的銀行系統中，AccountNumber數據點的格式通常比“單純的”整數值更複雜。有不同的格式安排，例如，對應於ISO 13616標准定義的國際銀行帳號 (IBAN) 的格式安排。顯然，這方面意味著（1）概念分析和後來的（2）邏輯定義需要更詳盡的方法。

說明性邏輯 SQL-DDL 聲明

然後，作為前面分析的推導，我聲明了一個邏輯設計，其中

  • 每個表代表一個實體類型，
  • 每列代表相應實體類型的屬性，並且
  • （以聲明方式）設置了多個約束，以保證保留在所有表中的行形式的斷言符合在概念層確定的業務規則。

我提供了註釋作為註釋，解釋了我認為對於上述結構特別重要的一些功能，如下所示：

-- You have to determine which are the most fitting 
-- data types and sizes for all your table columns 
-- depending on the business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient physical implementation settings; e.g.,
-- a good INDEXing strategy based on query tendencies.

-- As one would expect, you are free to make use of 
-- your preferred (or required) naming conventions. 

CREATE TABLE PartyType (
   PartyTypeCode CHAR(1)  NOT NULL, -- This one is meant to contain the meaningful values 'P', for 'Person', and 'O' for 'Organization'.
   Name          CHAR(30) NOT NULL,
   --
   CONSTRAINT PartyType_PK PRIMARY KEY (PartyTypeCode)
);

CREATE TABLE Party ( -- Represents the supertype.
   PartyId         INT       NOT NULL,
   PartyTypeCode   CHAR(1)   NOT NULL, -- Denotes the subtype discriminator.
   CreatedDateTime TIMESTAMP NOT NULL,  
   Etcetera        CHAR(30)  NOT NULL,  
   --
   CONSTRAINT Party_PK            PRIMARY KEY (PartyId),
   CONSTRAINT PartyToPartyType_FK FOREIGN KEY (PartyTypeCode)
       REFERENCES PartyType (PartyTypeCode)
);

CREATE TABLE Person ( -- Stands for a subtype.
   PersonId        INT      NOT NULL, -- To be CONSTRAINed as PRIMARY KEY and FOREIGN KEY at the same time, enforcing an association cardinality of one-to-zero-or-one from Party to Person.
   FirstName       CHAR(30) NOT NULL,
   LastName        CHAR(30) NOT NULL,
   GenderCode      CHAR(3)  NOT NULL,
   BirthDate       DATE     NOT NULL,
   Etcetera        CHAR(30) NOT NULL,  
   --
   CONSTRAINT Person_PK        PRIMARY KEY (PersonId),
   CONSTRAINT Person_AK        UNIQUE ( -- Composite ALTERNATE KEY.
       FirstName,
       LastName,
       GenderCode,
       BirthDate
   ),
   CONSTRAINT PersonToParty_FK FOREIGN KEY (PersonId)
       REFERENCES Party (PartyId)
);

CREATE TABLE Organization ( -- Represents the other subtype.
   OrganizationId  INT      NOT NULL, -- To be CONSTRAINed as PRIMARY KEY and FOREIGN KEY simultaneously, enforcing a association cardinality of one-to-zero-or-one from Party to Organization.
   Name            CHAR(30) NOT NULL,
   FoundingDate    DATE     NOT NULL,
   Etcetera        CHAR(30) NOT NULL,  
   --
   CONSTRAINT Organization_PK        PRIMARY KEY (OrganizationId),
   CONSTRAINT Organization_AK        UNIQUE      (Name), -- ALTERNATE KEY.
   CONSTRAINT OrganizationToParty_FK FOREIGN KEY (OrganizationId)
       REFERENCES Party (PartyId)
);

CREATE TABLE UserProfile (
   UserId          INT       NOT NULL, -- To be CONSTRAINed as PRIMARY KEY and FOREIGN KEY at the same time, enforcing an association cardinality of one-to-zero-or-one from Person to UserProfile.
   UserName        CHAR(30)  NOT NULL,
   CreatedDateTime TIMESTAMP NOT NULL,
   Etcetera        CHAR(30)  NOT NULL,  
   --
   CONSTRAINT UserProfile_PK         PRIMARY KEY (UserId),
   CONSTRAINT UserProfile_AK         UNIQUE      (Username),
   CONSTRAINT UserProfileToPerson_FK FOREIGN KEY (UserId)
       REFERENCES Person (PersonId)  
);

CREATE TABLE Account (
   AccountNumber   INT       NOT NULL,
   OwnerPartyId    INT       NOT NULL, -- A role name assigned to PartyId in order to depict the meaning it carries in the context of an Account.
   CreatedDateTime TIMESTAMP NOT NULL,
   Etcetera        CHAR(30)  NOT NULL,  
   --
   CONSTRAINT Account_PK        PRIMARY KEY (AccountNumber),
   CONSTRAINT AccountToParty_FK FOREIGN KEY (OwnerPartyId)
       REFERENCES Party (PartyId)
);

CREATE TABLE Transfer (
   TransferorAccountNumber INT       NOT NULL, -- Role name assigned to AccountNumber.
   TransfereeAccountNumber INT       NOT NULL, -- Role name assigned to AccountNumber
   TransferDateTime        TIMESTAMP NOT NULL,  
   Amount                  INT       NOT NULL, -- Retains the Amount in Cents, but there are other possibilities.
   Etcetera                CHAR(30)  NOT NULL, 
   --
   CONSTRAINT Transfer_PK             PRIMARY KEY (TransferorAccountNumber, TransfereeAccountNumber, TransferDateTime), -- Composite PRIMARY KEY.
   CONSTRAINT TransferToTransferor_FK FOREIGN KEY (TransferorAccountNumber)
       REFERENCES Account (AccountNumber),
   CONSTRAINT TransferToTransferee_FK FOREIGN KEY (TransfereeAccountNumber)
       REFERENCES Account (AccountNumber),
   CONSTRAINT AccountsAreDistinct_CK  CHECK       (TransferorAccountNumber <> TransfereeAccountNumber),
   CONSTRAINT AmountIsValid_CK        CHECK       (Amount > 0)
);

如前所述，沒有必要在任何基表的列中保留模棱兩可和有問題的 NULL 標記。

如果您想知道某個Transfer中涉及的**Account是否由Organization或Person擁有，您可以在單個 SELECT 語句中通過例如 the 、 the和 the列來獲取此類資訊。Transfer.TrasnferorAccountNumber``Account.PartyId``Party.PartyTypeCode

為了確保一方最多可以擁有一個帳戶（如註釋中所述），您應該為該Account.PartyId列修復一個 UNIQUE 約束。然而，在現實世界的場景中，例如，在銀行中，一個Person可以擁有零個一或多個Accounts，因此我認為一對零或一關聯似乎不現實。

如前所述，此答案中提出的方法應該用作您可以自行擴展和調整的參考。自然地，在概念層面進行的擴展和調整應該反映在邏輯模型中。

我在 (i)這個 db<>fiddle和 (ii)這個 SQL Fiddle中測試了這個結構的聲明，它們都在 PostgreSQL 9.6 上執行（你最初附加了這個數據庫管理系統的標籤）。

關於表格 Party、Person 和 Organization 的完整性和一致性考慮

對於上述佈局，必須保證每個“超類型”行始終由其對應的“子類型”對應物補充，進而確保所述“子類型”行與超類型“鑑別器”中包含的值兼容“ 柱子。

以聲明方式強制執行這種情況會非常方便和優雅，但不幸的是，沒有一個主要的 SQL 平台提供了這樣做的適當機制（據我所知）。因此，使用ACID TRANSACTIONS非常方便，以便在數據庫中始終自信地滿足這些條件。

類似場景

如果您對出現超類型-子類型結構的其他業務領域感興趣，您可能希望看到我的回答

相關資源

尾註

† 資訊建模集成定義( IDEF1X ) 是一種高度推薦的數據建模技術，於1993 年 12 月由美國國家標準與技術研究院(NIST)確立為*標準。*它牢固地基於 (a) 關係模型的創始人，即EF Codd 博士所撰寫的一些早期理論著作；(b)由PP Chen 博士開發的實體關係視圖；以及 (c) Robert G. Brown 創建的邏輯數據庫設計技術。

引用自：https://dba.stackexchange.com/questions/179654

相關問答

Postgresql

建模使用者和組 - 但具有多種類型的組

  • April 1, 2022
檢視問答
Postgresql

具有兩種可能的所有者/父類型的實體的數據庫模式？

  • June 14, 2018
檢視問答
Postgresql

儲存表名以引用其他表

  • May 27, 2018
檢視問答
Postgresql

使用表繼承而不是映射表

  • August 28, 2017
檢視問答
Postgresql

如果 PostgreSQL DB 或伺服器異常關閉，臟數據會發生什麼？

  • July 8, 2022
檢視問答
Postgresql

在單個 PostgreSQL 集群中使用數據庫而不是模式是否會降低性能？

  • June 13, 2022
檢視問答