Best practices suggestion for Invoicing table and Tax handling
<p>I am looking for suggestions on an <code>invoices / invoice_details</code> tables for a multi tenanted app whereby, some of the tenants will be registered for tax, and some will not.</p>

<p>The <code>invoice_details</code> table contains the following fields:</p>

<pre><code>id   description    amount_ex_tax   tax_amount   amount_inc_tax
</code></pre>

<p>For a tenant <strong>with tax enabled</strong>, a detail row may contain:</p>

<pre><code>id   description    amount_ex_tax  tax_amount  amount_inc_tax
1234 Repairs               100.00       20.00          120.00
</code></pre>

<p>For a tenant <strong>with tax disabled</strong>, a detail row may contain:</p>

<pre><code>id   description    amount_ex_tax  tax_amount  amount_inc_tax
1234 Repairs               100.00        0.00            0.00
</code></pre>

<p>This then creates the issue of not having one column to sum to find out an invoice value. </p>

<p>OPTION 1: Keeep the data as is, but the queries will have to be adjusted to ask for the right column based on the tenant type. This makes the various SQL queries more complex.</p>

<p>Option 2: A suggestion I have read says to set the <code>amount_inc_tax = amount_ex_tax</code> then the queries always sum the same column to get the value of the invoice. This simplifies the SQL. It would look like this:</p>

<pre><code>id   description    amount_ex_tax  tax_amount  amount_inc_tax
1234 Repairs               100.00        0.00          100.00
</code></pre>

<p>I think this suggestion confuses the code per se, as the column name does not contain what is infers.</p>

<p>OPTION 3: Another suggestion was to create a <code>total column</code> so that this is the column to always access for the total value, regardless of tax or no tax.</p>

<p>It would look like this:</p>

<pre><code>id   description    amount_ex_tax  tax_amount  amount_inc_tax  total_amount
1234 Repairs               100.00        0.00            0.00        100.00
</code></pre>

<p>What are the best / practices / suggestions that have worked best for you in this scenario? Which way will avoid which problems in future? Thanks for any advice!</p>