48 lines
1.7 KiB
MySQL
48 lines
1.7 KiB
MySQL
|
|
||
|
|
-- SQL script to create tables for VAT, sales tax, and country information
|
||
|
|
|
||
|
|
-- Create table for VAT rates and rules
|
||
|
|
CREATE TABLE vat_rules (
|
||
|
|
country_code TEXT PRIMARY KEY,
|
||
|
|
vat_rate REAL DEFAULT NULL,
|
||
|
|
reverse_charge BOOLEAN DEFAULT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Create table for customer rules related to VAT
|
||
|
|
CREATE TABLE customer_rules (
|
||
|
|
country_code TEXT,
|
||
|
|
customer_vat_registered BOOLEAN DEFAULT NULL,
|
||
|
|
vat_rate REAL DEFAULT NULL,
|
||
|
|
reverse_charge BOOLEAN DEFAULT NULL,
|
||
|
|
FOREIGN KEY (country_code) REFERENCES vat_rules(country_code)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Create table for vendor rules related to VAT
|
||
|
|
CREATE TABLE vendor_rules (
|
||
|
|
country_code TEXT,
|
||
|
|
vendor_vat_registered BOOLEAN DEFAULT NULL,
|
||
|
|
vat_rate REAL DEFAULT NULL,
|
||
|
|
reverse_charge BOOLEAN DEFAULT NULL,
|
||
|
|
FOREIGN KEY (country_code) REFERENCES vat_rules(country_code)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Create table for U.S. sales tax compliance
|
||
|
|
CREATE TABLE us_sales_tax_compliance (
|
||
|
|
state_code TEXT PRIMARY KEY, -- U.S. state code (e.g., CA, NY)
|
||
|
|
sales_threshold REAL, -- Sales threshold in USD (e.g., 100000)
|
||
|
|
transaction_threshold INTEGER, -- Number of transactions threshold (e.g., 200)
|
||
|
|
sales_tax_rate REAL, -- State sales tax rate (e.g., 7.25)
|
||
|
|
threshold_reached BOOLEAN, -- True/False if threshold is reached
|
||
|
|
sales_block BOOLEAN, -- True/False if sales to this state are blocked
|
||
|
|
total_sales REAL DEFAULT 0, -- Total sales in the state
|
||
|
|
total_transactions INTEGER DEFAULT 0 -- Total number of transactions in the state
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Create table for country and currency details
|
||
|
|
CREATE TABLE countries (
|
||
|
|
country_code TEXT PRIMARY KEY,
|
||
|
|
country_name TEXT,
|
||
|
|
continent TEXT,
|
||
|
|
currency TEXT
|
||
|
|
);
|