Store EU VAT rates in relational setup database

Given:

category_rates.json

"A_BOOKS_GEN": [
    {
      "NL": "standard"
    },
    {
      "BE": "reduced"
    },
    {
      "DK": "standard"
    },
  ],
"A_FURNITURE_GEN": [
    {
      "NL": "standard"
    },
    {
      "BE": "standard"
    },
    {
      "DK": "reduced"
    },
  ],

vat_rates.json

"NL": [
    {
      "rates": {
        "reduced": 9,
        "standard": 21
      }
    }
  ],
"DK": [
    {
      "rates": {
        "standard": 25
      }
    }
  ],

Product.php

public function category(): BelongsTo
{
    return $this->belongsTo(Category::class);
}

products table

| id  | category_id | sku     | title    |
------------------------------------------
| 1   | 1           | 12345   | product1 |
| 2   | 2           | 54321   | product2 |

Category.php

public function products(): HasMany
{
    return $this->hasMany(Product::class);
}

categories table

| id  | name       |
--------------------
| 1   | books      |
| 2   | furniture  |

And using a package to get the actual VAT:

$rate = Rate::country('NL', 'standard'); // returns 21
$rate = Rate::country('NL', 'reduced'); // returns 9

Also using this category schema that Amazon uses here Each category has a VAT rate for a country.

Use case: at the end of each month, the webapp will grab all orders. An order contains products and has country (shipping) information. I'll loop through all the products of an order and have to determine what the VAT rate for each product is. In this state I already have the Product, the Category and the Country. With these models, I need to get the VAT rate (the actual number) and the VAT type (standard/reduced). I can get the VAT type, because I have the Country and the Category. This is where I'm stuck as I'm not sure on how to code that.

How do I get the VAT rate (the actual integer value) for each product that is sold? Bear in mind that each product sold has a Country and a Category.

Example:

Product1 has Category books and is sold in NL (country information from the order). According to this schema, we see that Category books has a reduced rate for NL. And in vat_rates.json we see that the VAT for reduced type in NL is 9. We have Product, Country and Category information, but the rate type ('reduced') and the VAT (9) is still missing.

$country = $order->countryCode;
$product = $order->products->first();
$category = $product->category;
$vatRate = // either standard/reduced/etc?
$vat = // the actual integer value

I was thinking of getting the table from Amazon and making these relations out of it:

category_country_types

| id  | category_id | country_id | type     |
---------------------------------------------
| 1   | 1           | 1          | standard |
| 2   | 2           | 5          | reduced  |

But I'm not sure whether I'm on the right track here. There are a lot of categories, which have rates for each EU country, so this would be a big table.

pdigital

3 years ago

Level 1

Solved it by creating like so (note that this is a simplified example)

An enum class which holds a Vat Rate value:

enum VatRate: string
{
    case STANDARD = 'standard_rate';
    case REDUCED = 'reduced_rate';
    case REDUCED_ALT = 'reduced_rate_alt';
    case SUPER_REDUCED = 'super_reduced_rate';
    case ZERO = 'zero_rate';
}

And creating a Facade which calculates VAT:

class VatHelper
{
    private function percentage(VatRate $rate, string $country): int
    {
         return $this->rates()[$country][$rate->value];
    }

    private function rates(): array
    {
         $json = file_get_contents($this->ratesPath);
         return json_decode($json, true)['rates'];
    }

    public function priceExcludingVat(
        int|float $priceIncludingVat,
        VatRate $rate = VatRate::STANDARD,
        string $country,
        bool $round
    ): int|float {
        $transformedPercentage = 1 + ($this->percentage($rate, $country) / 100); // will transform rate of 21.00 to 1.21
        return $priceIncludingVat / $transformedPercentage;
    }
}

And I ended up saving all rates locally in a JSON file.

Please or to participate in this conversation.