不同VAT下更新现有价格：PHP实现vs数据库触发器方案对比

Absolutely, this approach is feasible for your immediate CSV export needs—but it’s worth weighing the tradeoffs and considering alternative patterns that might save you headaches down the line.

Pros of Pre-Storing Calculated VAT Fields #

• Dead-simple implementation: The logic is straightforward (netPrice * 1.2/1.19), and your export tool can just pick the pre-computed fields directly without extra processing during export.
• Quick access: If you ever need to pull up a product’s含税 price for quick checks, you don’t have to recalculate it every time.

Cons to Watch Out For #

• Data redundancy: Storing computed values eats up unnecessary database space. While it’s negligible for small datasets, it adds up with thousands/millions of products.
• Sync risks: If you update a product’s netPrice, you must remember to re-calculate and update both priceVAT20 and priceVAT19. Miss this step, and your exported CSVs will have outdated, incorrect prices.
• Scalability issues: If you later need to support more regions (e.g., France’s 20% VAT, Poland’s 23%), you’ll have to keep adding new fields to your table—leading to a bloated schema over time.

Better Alternatives (Long-Term) #

If you want to avoid the above pitfalls while keeping your export workflow simple, consider these options:

1. Calculate on-the-fly during export
   This is the most flexible approach. When generating your CSV, compute the VAT prices directly in your query or export script:

   SELECT
       product_id,
       name,
       netPrice,
       netPrice * 1.2 AS priceVAT20,
       netPrice * 1.19 AS priceVAT19
   FROM products;
   

   No extra storage, no sync issues, and adding new taxes only requires updating the export logic.

2. Use a database view
   Create a view that includes the computed VAT fields, so you can query it like a regular table for exports:

   CREATE VIEW products_with_vat AS
   SELECT
       *,
       netPrice * 1.2 AS priceVAT20,
       netPrice * 1.19 AS priceVAT19
   FROM products;
   

   This gives you the convenience of "selecting pre-made fields" without storing redundant data—any updates to netPrice automatically reflect in the view.

Final Verdict #

Pre-storing the VAT fields works if your product catalog is small, tax rates never change, and you’re confident you’ll always sync updates to netPrice with the computed fields. But for most cases, calculating on export or using a view is a more maintainable, scalable choice.

内容的提问来源于stack exchange，提问作者Dextranovich