title: Used store credit in SQL database design
body: <p>Consider these two use cases.</p>
  
  <p>Say a store gives out store credit for customers. Store credit come from refunds, discounts, overpaying amounts etc... </p>
  
  <ol>
  <li><p>The customer decides how and when to
  use this credit. E.g. Say customer A
  have $50 dollars in store credit and
  he decides to use $25 for invoice A
  and $25 for invoice B... or decides
  to have the store credit sitting
  there against his account for future
  purchases.</p></li>
  <li><p>The customer and store must also
  have a way to look up the total of
  store credit.</p></li>
  </ol>
  
  <p><strong>What is the easiest and most straight forward way to cater for this in table structure?</strong> </p>
  
  <p>I currently have tables:</p>
  
  <ul>
  <li>Transaction (For journal and reporting purpose)  </li>
  <li>Customer</li>
  <li>Credit</li>
  <li>Used Credit</li>
  </ul>
  
  <p>Invoice table will have other associations like invoice lines, receipt, receipt lines etc...</p>
  
  <p>Is this the best way to do this? My concern is primarily regarding ease of reporting and transaction mechanics. </p>
  
  <p>Alternatively I'm considering just saving all credit entry and used credit inside the transaction table.</p>
  
  <p><strong>Updated diagram for clarification and from inputs</strong></p>
  
  <p><img src="https://i.stack.imgur.com/5Os7z.png" alt="enter image description here"></p>