We're having a problem coming up with the proper schema design such
that foreign key constraints may be applied.
The system has multiple tables to which records are inserted
whenever a customer or system transaction occurs. There are
different transaction tables that each have different columns
depending on data relevant to that transaction type.
Many (but not all) of the transactions will result in customer
and/or system accounts being debited/credited for various fees/etc.
These debits/credits are inserted to one of several ledger tables.
Which ledger table depends on the account type being debited/credited.
The reference number in the ledger tables should refer back
to the reference number of the original transaction.
But how to apply foreign key constraint to reference number
in the ledger table since don't know which transaction table
the transaction is in?
Example
Multiple tables (of different layout) are used to store
raw data about a number of different transaction types.
For example,
Table SALE_TRANSACTIONS
ref_num number not null, <=== primary key
price number not null,
qty number not null,
cust_num number not null,
trans_date date not null
Table DIVIDEND_TRANSACTIONS
ref_num number not null, <=== primary key
decl_date date not null,
rec_date date not null,
pay_date date not null,
ex_date date not null,
amount number not null,
cusip number not null
One or more ledger tables (each w/same layout) are used to record
debits and credits to accounts which occur as the result of some
of the raw transactions recorded in the transaction tables above.
For example,
Table GENERAL_LEDGER
ref_num number not null, <=== want this to be id of transaction
cr_acct_id number not null,
db_acct_id number not null,
amount number
Note #1: Reference number (ref_num) in ledger table refers
back to source transaction record in one of the transaction tables
(for example, in SALE_TRANSACTIONS or IVIDEND_TRANSACTIONS).
Note #2: A single transaction in a transaction table may
result in multiple postings to the ledger table (for example,
credit buyer acct, debit seller acct, credit business acct for
commission, credit tax withholding acct, etc).
Note #3: Not all postings to the transaction tables necessitate in
a posting(s) to the ledger table(s).
Note #4: Insert performance is key, in our system the transaction and
ledger tables have a high volume of inserts executed against them.
