When designing a stock management database system for sales and purchases what would be the best way to store the various taxes and other such amounts?
A few of the fields that could be saved are:
Currently the most reasonable solution so far is storing down (roughly) item, quantity, total excluding tax (rounded), and the total tax (rounded).
Is there a better way of storing these details for a generic system?
Given the system needs to be robust, what should be done if there were multiple tax values which might need to be separated (e.g. state and city)? In this case a separate table would be in order, but would it be considered excessive to just have a rowID and some taxID mapping to a totalTax column?
To clarify: Asking how to store the data about individual transactions and that side; not so much the details about the tax specific rates.
To record received sales tax from customers, debit your Cash account, and credit your Sales Revenue and Sales Tax Payable accounts. When you remit the sales tax to the government, you can reverse your initial journal entry. To do this, debit your Sales Tax Payable account and credit your Cash account.
Keep records for 3 years from the date you filed your original return or 2 years from the date you paid the tax, whichever is later, if you file a claim for credit or refund after you file your return. Keep records for 7 years if you file a claim for a loss from worthless securities or bad debt deduction.
Supporting documents include sales slips, paid bills, invoices, receipts, deposit slips, and canceled checks. These documents contain the information you need to record in your books. It is important to keep these documents because they support the entries in your books and on your tax return.
Receipts are issued in business-to-business dealings as well as stock market transactions. Receipts are also necessary for tax purposes as proof of certain expenses.
The problem with approach is if the tax changes, VAT (sales tax) in the UK has changed twice in the last 12 months.
When I worked in ECommerce websites we had a table Tax_Rate
which held the different tax rates a shop would deal with eg.
and then your stock table fields may have
your invoice_detail row table will be
your invoice table will be
Where fk_denotes a foreign key. Note that OrderId will not be unique in your invoice row table.
EDITS: Head's all over the place today.
You need to denormalize the invoice row total and the tax rate total because you don't want future changes in an item price or tax rate to affect the historical invoices.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With