StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <pre><code>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) ...,) </code></pre> <p>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.</p> <p>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. <a href="https://stackoverflow.com/questions/217945/can-i-have-multiple-primary-keys-in-a-single-table">see here</a></p> <p>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).</p> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload