title: online credit system table layout for transaction destinations foreign key
body: <p>I'm trying to figure out how to properly store foreign keys when the key points to a different table based on a type.</p>
  
  <p>I <a href="https://stackoverflow.com/questions/5100386/personal-finance-app-database-design">searched</a>
   but <a href="https://stackoverflow.com/questions/4074425/accounting-database-storing-credit-and-debit">nothing</a>
   I <a href="https://stackoverflow.com/questions/4074737/accounting-database-storing-a-transaction">found</a>
   <a href="https://stackoverflow.com/questions/4415022/database-design-accounting-transaction-table">seemed</a>
   to <a href="https://stackoverflow.com/questions/2494343/database-schema-design-for-a-double-entry-accounting-system">help</a>
   <a href="http://www.databaseanswers.org/data_models/index.htm" rel="nofollow noreferrer">at</a>
   <a href="http://www.cuppadev.co.uk/double-entry-accounting-in-rails/" rel="nofollow noreferrer">all</a>.</p>
  
  <p>There is a single table that will store most of the basics for the credits:</p>
  
  <pre><code>user_accounting
  --------------------
  user_accounting_id      (PK)
  user_id                 (FK)
  amount              
  type                    (+deposit, +credit, -transfer, -purchase)
  credit                  (bool, so I don't have to always check if amount is &lt; or &gt; 0)
  void                    (in case it was canceled and repaired with some other transaction)
  date
  details                 (notes)
  </code></pre>
  
  <p><strong>type</strong> s:</p>
  
  <pre><code>deposit - for outsite money being put into the system.
  credit - are for money being transfered into their account from another
  transfer - for putting money into someone elses account
  purchase - for purchasing a site item
  </code></pre>
  
  <p>So far so good.  This next part I'm a bit befuddled with.  It will also have to store where the money is going to or coming from.</p>
  
  <p>I'd like to store the foreign key for whatever <strong>type</strong> would indicate it's from.
  So if it's a purchase it will store the FK for the invoice_id, if it's a deposit it'll store a transaction_id from the merchant provider, if it's a transfer it'll store the user_accounting_id of the credit, if it's a credit it'll store the user_accounting_id of the transfer.</p>
  
  <p>I'd  be nice to have a single column storing that:</p>
  
  <pre><code>user_accounting (con't)
  -----------------------------
  source_or_destination_id (FK)
  </code></pre>
  
  <p>But I know that I can't have a single column be a foreign key linking to different tables based on <strong>type</strong>.  So I could just store it as an (int) but it would be a <em>huge pain</em> to try to do JOINs with that id with different tables based on the <strong>type</strong>.  Tried to do that a long time ago, big mistake.</p>
  
  <p>So instead, I could do this instead:</p>
  
  <pre><code>user_accounting (con't)
  -----------------------------
  invoice_id                  (FK)
  transaction_id              (FK)
  credit_user_accounting_id   (FK)
  transfer_user_accounting_id (FK)
  </code></pre>
  
  <p>But yet again that a big problem since I create an exclusive arc which isn't good.</p>
  
  <p>I could also use a many_to_many_through relation for the <strong>type</strong> but the pivot table would still have the same problem of storing foreign keys in the same spot for multiple tables.</p>
  
  <p>Maybe I could simply store different types of transactions in different tables entirely,
  I could have:</p>
  
  <pre><code>user_accounting_deposit, user_accounting_credit, user_accounting_transfer, user_accounting_purchase.
  </code></pre>
  
  <p>And then the foreign keys wouldn't be a problem.  Of course to figure out anyones account balance I'd have to do joins and sums from a bunch of tables now.</p>
  
  <p>Perhaps there's an entirely different better way of doing it.  I don't care how many tables it takes.  Chances are I'm over complicating something somewhere.</p>
  
  <p>Thanks</p>