Primary Keys are a Foreign Key of two different mutually exclusive tables

Question

I'm having a problem on how to design a table that must have a Primary Key which is a foreign key to two mutually exclusive tables.

+----------------+            +-----------------+
|       PARTY    |            + PERSON          +
+----------------+            +-----------------+
| Unique ID (PK) | -|------O|-| Unique ID (FK)  |
| Party Type     |            | some details    |
|                |            +-----------------+
|                |            
|                |            +-----------------+
|                |            | BUSINESS        |
|                |            +-----------------+
|                |            | Scheme Code (PK)|
|                | -|------O|<| Serial No (PK)  |
|                |            | Unique ID (FK)  |
|                |            | some details    |
+----------------+            +-----------------+ 

+-----------+                 +-----------------+ 
| PERSON    | -|-----------O|<| BANK ACCOUNT    | 
+-----------+                 +-----------------+           
                              | Account No (PK) |
+-----------+                 | Unique ID (FK)  |           
| BUSINESS  | -|-----------O|<| Scheme Code (FK)|
+-----------+                 | Serial No (FK)  |           
                              | some details    |
                              +-----------------+

1. There are 4 entities. A PARTY can be a PERSON or BUSINESS (but cannot be both at the same instance). A PERSON can have one or more BANK ACCOUNT and a BUSINESS can also have one or more BANK ACCOUNT.

2. For a PERSON, only 1 Unique ID can be assigned.

3. For a BUSINESS, it can share the same unique PARTY id given it has a unique combination for (Scheme Code, Serial No, and Unique Id)

4. PARTY's primary key is 'Unique ID'

5. PERSON's primary key is 'Unique ID'

6. BUSINESS' primary keys are 'Unique ID', 'Scheme Code', and 'Serial No'

7. BANK ACCOUNT's primary keys are a combination of all they keys of PERSON and BUSINESS with 'Unique ID' as the only common foreign key and 'Scheme Code'/'Serial No' is a foreign key exclusive only from BUSINESS.

I thought the problem would be in the BANK ACCOUNT entity. If the PARTY is a BUSINESS, there will be no problem since all primary/foreign keys are populated (Unique ID, Scheme Code, Serial No). However, if a PARTY is a PERSON, only Unique ID will be populated and the other two keys will have NULL values. And I think NULL primary keys are not allowed.

Answer 1

Make BANK_ACCOUNT refer to PARTY instead of either BUSINESS or PERSON so long as you guarantee that PARTY is 1:1 with BUSINESS/PERSON.

CREATE TABLE PARTY (
party_id INT PRIMARY KEY)

CREATE TABLE PERSON (
   person_id INT PRIMARY KEY,
   party_id INT FOREIGN KEY REFERENCES PARTY(party_id),
   ...,)

CREATE TABLE BUSINESS (
   business_id INT PRIMARY KEY,
   party_id INT FOREIGN KEY REFERENCES PARTY(party_id),
   scheme_code <TYPE>,
   serial_no <TYPE>,
   ...,)

CREATE TABLE BANK_ACCOUNT (
   account_no INT PRIMARY KEY,
   party_id INT FOREIGN KEY REFERENCES PARTY(party_id)
   ...,)

To enforce business rule #1, where a PARTY can be a BUSINESS or PERSON but not both: you need to implement in a trigger/program by checking for the PARTY.party_id in BUSINESS or PERSON.

For rule #2, where PARTY is strictly 1:1 with PERSON, create either a composite/multi-column primary key on (PERSON.person_id, PERSON.party_id) or a unique index. see here

For rule #3, same solution as rule #2. You can omit BUSINESS.business_id if you can have a composite primary key on (BUSINESS.party_id, BUSINESS.scheme_code, BUSINESS.serial_no).

Rule #7 becomes irrelevant if you can do all of the above. You will not need to have columns in BANK_ACCOUNT for scheme_code/serial_no. Simply create a view and look for those values through a join using PARTY.party_id.