Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money

In the business context of relevance, both members and organizations need to have an account for funds. Funds can be transferred

• from member to member,

• from member to organization,

• from organization to organization, and

• from organization to member.

Considerations

In order to build a database for such a scenario, I have created the following three tables:

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 
);

I think that the TransferHistory table is necessary to show who/what sent funds to whom/what.

The problem is, since Members and Organizations are different tables, how can I reference them from the TransferHistory table?

For instance, the data involved can show up as follows:

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

That would suggest that accounts need to be recorded in the same table, but accounts are for two different kinds of owners (members and organizations), each of which are retained in their respective table.

I could create a table called Accounts, so now I would have four tables:

```
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 Tran

If the intention is to build a relational database, it is really helpful to first carry out (a) an analysis of the business context of interest —in order to delineate a conceptual schema— in terms of entity types, inspecting their properties and associations before (b) thinking in terms of tables, columns and constraints —aspects that correspond to the logical level—. Following this course of action, it is much simplier to capture the meaning of the business domain with accuracy and then reflect it in an actual, well constrained, SQL-DDL design.

One of the numerous advantages offered by the relational paradigm is that it permits managing the data in its natural structure; hence one has to “find” such structure before employing relational instruments to manage it. It does not matter if the scenario at issue is related to a personal project (as you pointed out via comments): the more realistic you define it, the more you will learn from its development (if that is the purpose of this effort). Of course, a realistic personal project may evolve into a comercial one with relatively minor adaptations.
Business rules

In order to present a first progression that you may like to use as a reference, I have formulated some of the conceptual-level business rules that are among the most significant ones, and they are enumerated as follows:

• A Person owns zero-one-or-many Accounts

• A Person is primarily distinguished by its Id

• A Person is alternately distinguished by his/her Firstname, Lastname, BirthDate and Gender

• An Organization owns zero-one-or-many Accounts

• An Organization is primarily differentiated by its Id

• An Organization is alternately differentiated by its Name

• An Organization starts operating at a FoundingDate

• An Account is the Transferor in zero-one-or-many Transfers

• An Account is the Transferee in zero-one-or-many Transfers

• An Account is primarily identified by its Number

• An Account is issued at an exact CreatedDate

• In a Transfer, the Transferor must be different from the Transferee

• A Person may log in via zero-or-one UserProfile

Since the associations —or relationships— (1) between Person and Account and (2) between Organization and Account are very similar, this fact reveals that Person and Account are entity subtypes of Party (basically, either an individual or a group of individuals), which is in turn their entity supertype. This is a classic information structure that arises very frequently in multiple conceptual models of diverse kinds. In this manner, two new rules can be asserted:

• A PartyType classifies zero-one-or-many Parties

• A Party is either a Person or an Organization

And two of the previous business rules can be consolidated into a single one:

• A Party owns zero-one-or-many Accounts

Which can be also be stated from the point of view of the Account entity type:

• An Account is owned by exactly-one Party

Expository IDEF1X diagram

Consequently, I created an expository (simplified) IDEF1X† diagram that synthesizes the rules formulated above, and it is shown in Figure 1:

Party, Person and Organization: Supertype-subtype structure

As demonstrated, Person and Organization are depicted as mutually exclusive subtypes of Party.

The Party supertype holds a discriminator (i.e., PartyTypeCode) and all the properties (or attributes) that are common to its subtypes, which, in turn, have the properties that apply to each of them.

Account

The Account entity type is directly connected with Party, which provides a subsequent connection between (i) Account and Person, and between (ii) Account and Organization.

Since it is possible that, in the real world, (a) a bank Account is not transferable, i.e., its Owner cannot change and (b) an Account cannot start being current or enabled without an Owner, the PRIMARY KEY of this entity type may be comprised of the properties PartyId and AccountNumber, so you should analyze the scenario even more thoroughly to define this point with high precision.

Transfer

On the other hand, the Transfer entity type presents a composite PRIMARY KEY made up of three properties, i.e., TransferorAccountNumber, TransfereeAccountNumber (role names I assigned to distinguish every one of two Account properties involved in each Transfer instance) and TransferDateTime (which tells the exaxct Instant when a Transfer occurrence was performed).

Factors about AccountNumbers

Be also aware that, in actual banking systems, the format of an AccountNumber data point is usually more complex than a “mere” integer value. There are different format arrangements, e.g., the one that corresponds to the International Bank Account Number (IBAN), defined by the ISO 13616 standard. This aspect implies, evidently, that the (1) conceptual analysis and the later (2) logical definitions require a much more exhaustive approach.
Illustrative logical SQL-DDL declarations

Then, as a derivation from the previous

StackExchange Database Administrators Q#179654, answer score: 15