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 TransferHistory (
"from" integer references Accounts(accountid),
"to" integer references Accounts(accountid),
quantity integer
);
…but now I have to ensure that each foreign key from Members
and Organizations
tables does not point to the same Account
row in Accounts
table…
…or I could have an Accounts
table having two foreign keys, one pointing to Members
and another to Organizations
(and one foreign key column would have to contain a NULL mark at all times). But now things get a bit confusing with respect to queries. The database design would be, generally speaking, as follows:
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
);
So, does anybody have a suggestion on how to solve this problem?
Best Answer
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:
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:
And two of the previous business rules can be consolidated into a single one:
Which can be also be stated from the point of view of the Account entity type:
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
andOrganization
are depicted as mutually exclusive subtypes ofParty
.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 withParty
, which provides a subsequent connection between (i)Account
andPerson
, and between (ii)Account
andOrganization
.Since it is possible that, in the real world, (a) a bank
Account
is not transferable, i.e., itsOwner
cannot change and (b) anAccount
cannot start being current or enabled without anOwner
, the PRIMARY KEY of this entity type may be comprised of the propertiesPartyId
andAccountNumber
, 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 twoAccount
properties involved in eachTransfer
instance) andTransferDateTime
(which tells the exaxct Instant when aTransfer
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 analysis, I have declared a logical design where
I have supplied notes as comments that explain some of the features that I esteem particularly important with respect to the aforementioned structure, that is shown below:
As noted, there is no need for the retention of ambiguous and problematic NULL marks in the columns of any of the base tables.
If you want to know if an Account involved in a certain Transfer is owned by an Organization or a Person, you can derive such information in a single SELECT statement via, e.g., the
Transfer.TrasnferorAccountNumber
, theAccount.PartyId
, and theParty.PartyTypeCode
columns.So that you can ensure that a Party can own at most one Account (as stated in comments), then you should fix a UNIQUE constraint for the
Account.PartyId
column. However, in real world scenarios, e.g., in a bank, a Person can own zero-one-or-many Accounts, thus I esteem that a one-to-zero-or-one association does not appear realistic.As previously mentioned, the approach proposed in this answer is supposed to be utilized as a reference that you can extend and adapt on your own. Naturally, extensions and adaptations made at the conceptual level should be reflected in the logical model.
I tested the declaration of this structure in (i) this db<>fiddle and in (ii) this SQL Fiddle, both running on PostgreSQL 9.6 (you initially attached the tag of this database management system).
Integrity and consistency considerations regarding the tables Party, Person and Organization
With the layout described above, one must guarantee that each “supertype” row is at all times complemented by its corresponding “subtype” counterpart and, in turn, ensure that said “subtype” row is compatible with the value contained in the supertype “discriminator” column.
It would be very handy and elegant to enforce such circumstances declaratively but, unfortunately, none of the major SQL platforms has provided the proper mechanisms to do so (as far as I know). Therefore, it is quite convenient to make use of ACID TRANSACTIONS so that these conditions are always confidently met in a database.
Similar scenarios
In case you are interested in other business domains in which supertype-subtype structures come about, you might like to see my answers to
Relevant resource
Transfer.Amount
, in PostgreSQL.Endnote
† Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the early theoretical works authored by the originator of the relational model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.