如何在数据库中存储税收？ [英] How to store taxes in database?
问题描述
我需要在我的项目中添加每个省/州的税。
我正在辩论是否应该在省和州之间添加多对多关系。税收，或仅向每个省添加一个tax1_name，tax1_rate，tax2_name，tax2_rate。我认为任何地方的税均不超过2种？
我还需要在购买时随每张发票存储税率。
所以我的选择是添加2个多对多表或添加8个字段。您会选择哪一个？为什么？
或者我可以只交1税。我认为发票上显示为 GST + PST不会太糟糕。这可以解决愚蠢的魁北克省的问题，魁北克省在GST之上收取QST（税！）。
可能只有1个多对多表格，并存储每种税款的开始日期和结束日期，然后在生成发票时，我可以根据日期进行查找。
您真正需要的是这样的东西：
将总税合并到一个字段中的问题是，尽管您可能会选择可能无法避免在收据上以这种方式显示给客户，各个司法管辖区都希望您和您的会计师分别跟踪所收取和应支付的税款。因此，混合费率将无法使用，因为您需要汇总金额，以便您可以向税务员付款并让他们的审计师满意。
在我为您建议的问题解决方案中，请注意 TAX_RATE 表包括税收和管辖权之间的交集。这样一来，费率不仅可以在一个辖区变化，而且可以随时间变化。
请注意，税率表包含 calculation_order 值。您可以随意排序和比较。我以前用过整数。这个想法是您从第一个开始按顺序应用税收。如果您有两种税费适用于相同的基本金额，则这些税费具有相同的计算顺序。如果您在第一笔税款的基础上缴纳第二笔税款，那么第二笔税则具有更高的计算顺序。
最后，我提供了一个表格，可让您跟踪随着时间的流逝免税。您可以用不同的方式来处理，而我绘制的方法可能不是最好或最简单的方法。关键是要包含一种机制，说产品X在给定日期范围内（或不）获得税Y。
I need to add per-province/state taxes to my project.
I'm debating whether I should add a many-to-many relationship between Provinces and Taxes, or just add a tax1_name, tax1_rate, tax2_name, tax2_rate to each province. I don't think any place has more than 2 taxes?
I will also need to store the tax rate at the time of purchase with each invoice.
So my options are add 2 many-to-many tables, or add 8 fields. Which would you go with, and why?
Or I could just have 1 combined tax. I don't think it would be too bad if it showed on the invoice as "GST + PST". This would solve the issue with stupid Quebec which charges QST on top of GST (tax on tax!).
Or I could just have 1 many-to-many table and store the start and end date of each tax and then when I generate the invoice I could look it up based on date.
What you really need is something like this:
The problem with combining the total tax into a single field is that while you may or may not get away with showing it that way on the receipt to a customer, the various jurisdictions are going to expect you and your accountants to track the tax collected and payable separately. Blended rates therefore won't do because you need the amounts broken out so you can pay the tax man and satisfy their auditors.
In my suggested solution to your problem, note that the TAX_RATE table includes an intersection between the tax and the jursidiction. This is so the rate can change, not only from jurisdiction to jurisdiction, but so that it can change over time.
Note too that the tax rate table includes a calculation_order value. This can be anything you like which can be sorted and compared. I've used integers before. The idea is that you apply the taxes in order starting with the first one. If you have two taxes that are applied to the same base amount, then these taxes have the same calculation order. If you pay tax two on top of tax one, then tax two has a higher calculation order.
Lastly, I've included a table that allows you to keep track of tax exemptions over time. You can handle this different ways and the way I've drawn it may not be the best or easiest. The point is to include a mechanism for saying "product X does (or doesn't) get tax Y (over a given date range)".
这篇关于如何在数据库中存储税收？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
