Re: Foreign key

"bof" <bofnospam_at_yahoo.com> wrote in message news:e0c8de56.0301301506.1f4331fe_at_posting.google.com...
> Question
>
> 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.

My solution to this has been to have a single ledger table, with an intersect table between each transaction table and the ledger table. To expand on your example:

create table general_ledger (
  /* i would use a datetime if resolution was sufficient, else sequence nbr */
  ledger_id number not null primary key,   cr_acct_id number not null,
  db_acct_id number not null,
  amount number
)

create table sale_ledger (
  ledger_id number not null primary key

      constraint foreign key references general_ledger (ledger_id),   ref_num number not null

      constraint foreign key references sale_transactions )

create table dividend_ledger (
  ledger_id number not null primary key

      constraint foreign key references general_ledger (ledger_id),   ref_num number not null

      constraint foreign key references dividend_transactions )

Using the ledger_id as the primary key for the sale_ledger and divident_ledger tables handles the case if there can be more than one general_ledger entry - I do this in my case to record changes to the sale_transactions or dividend_transactions entries. Received on Fri Jan 31 2003 - 01:44:24 CET