Sorry, didn't make it last night, went to bed early for a change.
This is the sql I use for my Dutch customers. I is the most basic set-up. but easy to expand.
There are three zones definitions
- the first is for the country the shop is located in
- the second for all countries in the EU (except your own)
- the third for the rest of the world
In this basic there is one Tax class
For each zone definition you need a tax rate (in the Netherlands it is):
- 21% for the Netherlands
- 21% for all EU countries
- 0% for the rest of the world
If you are also selling to companies with a valid VAT number inside the EU you can use this old addon. (I will post an updated version for 1.5.x later this week)
It will set the VAT for companies inside the EU to Zero when a valid tax number is used during registration.
--
-- Table structure for table `geo_zones`
--
DROP TABLE IF EXISTS `geo_zones`;
CREATE TABLE IF NOT EXISTS `geo_zones` (
`geo_zone_id` int(11) NOT NULL,
`geo_zone_name` varchar(32) NOT NULL DEFAULT '',
`geo_zone_description` varchar(255) NOT NULL DEFAULT '',
`last_modified` datetime DEFAULT NULL,
`date_added` datetime NOT NULL DEFAULT '0001-01-01 00:00:00'
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
--
-- Dumping data for table `geo_zones`
--
INSERT INTO `geo_zones` (`geo_zone_id`, `geo_zone_name`, `geo_zone_description`)
VALUES
(1, 'Nederland', 'Nederlandse btw'),
(2, 'Alle landen binnen de EU', 'Intracommunicataire landen'),
(3, 'Alle landen buiten EU', 'De rest van de wereld');
-- --------------------------------------------------------
--
-- Table structure for table `tax_class`
--
DROP TABLE IF EXISTS `tax_class`;
CREATE TABLE IF NOT EXISTS `tax_class` (
`tax_class_id` int(11) NOT NULL,
`tax_class_title` varchar(32) NOT NULL DEFAULT '',
`tax_class_description` varchar(255) NOT NULL DEFAULT '',
`last_modified` datetime DEFAULT NULL,
`date_added` datetime NOT NULL DEFAULT '0001-01-01 00:00:00'
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
--
-- Dumping data for table `tax_class`
--
INSERT INTO `tax_class` (`tax_class_id`, `tax_class_title`, `tax_class_description`)
VALUES
(1, 'BTW Tarief hoog', 'BTW Tarief hoog');
-- --------------------------------------------------------
--
-- Table structure for table `tax_rates`
--
DROP TABLE IF EXISTS `tax_rates`;
CREATE TABLE IF NOT EXISTS `tax_rates` (
`tax_rates_id` int(11) NOT NULL,
`tax_zone_id` int(11) NOT NULL DEFAULT '0',
`tax_class_id` int(11) NOT NULL DEFAULT '0',
`tax_priority` int(5) DEFAULT '1',
`tax_rate` decimal(7,4) NOT NULL DEFAULT '0.0000',
`tax_description` varchar(255) NOT NULL DEFAULT '',
`last_modified` datetime DEFAULT NULL,
`date_added` datetime NOT NULL DEFAULT '0001-01-01 00:00:00'
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
--
-- Dumping data for table `tax_rates`
--
INSERT INTO `tax_rates` (`tax_rates_id`, `tax_zone_id`, `tax_class_id`, `tax_priority`, `tax_rate`, `tax_description`)
VALUES
(1, 1, 1, 1, 21.0000, 'Btw 21%'),
(2, 2, 1, 2, 21.0000, 'Btw 21%'),
(3, 3, 1, 3, 0.0000, 'Btw 0%');
-- --------------------------------------------------------
--
-- Table structure for table `zones_to_geo_zones`
--
DROP TABLE IF EXISTS `zones_to_geo_zones`;
CREATE TABLE IF NOT EXISTS `zones_to_geo_zones` (
`association_id` int(11) NOT NULL,
`zone_country_id` int(11) NOT NULL DEFAULT '0',
`zone_id` int(11) DEFAULT NULL,
`geo_zone_id` int(11) DEFAULT NULL,
`last_modified` datetime DEFAULT NULL,
`date_added` datetime NOT NULL DEFAULT '0001-01-01 00:00:00'
) ENGINE=MyISAM AUTO_INCREMENT=242 DEFAULT CHARSET=utf8;
--
-- Dumping data for table `zones_to_geo_zones`
--
INSERT INTO `zones_to_geo_zones` (`association_id`, `zone_country_id`, `zone_id`, `geo_zone_id`)
VALUES
(1, 150, NULL, 1),
(2, 14, 0, 2),
(3, 21, 0, 2),
(4, 240, 0, 3),
(5, 57, 0, 2),
(6, 73, 0, 2),
(7, 81, 0, 2),
(8, 27, 0, 2),
(9, 33, 0, 2),
(10, 55, 0, 2),
(11, 67, 0, 2),
(12, 72, 0, 2),
(13, 84, 0, 2),
(14, 97, 0, 2),
(15, 103, 0, 2),
(16, 105, 0, 2),
(17, 53, 0, 2),
(18, 117, 0, 2),
(19, 123, 0, 2),
(20, 124, 0, 2),
(21, 132, 0, 2),
(22, 170, 0, 2),
(23, 171, 0, 2),
(24, 175, 0, 2),
(25, 190, 0, 2),
(26, 189, 0, 2),
(27, 195, 0, 2),
(28, 56, 0, 2),
(29, 222, 0, 2),
(30, 203, 0, 2),
(31, 1, 0, 3),
(32, 2, 0, 3),
(33, 3, 0, 3),
(34, 4, 0, 3),
(35, 5, 0, 3),
(36, 6, 0, 3),
(37, 7, 0, 3),
(38, 8, 0, 3),
(39, 9, 0, 3),
(40, 10, 0, 3),
(41, 11, 0, 3),
(42, 12, 0, 3),
(43, 13, 0, 3),
(44, 15, 0, 3),
(45, 16, 0, 3),
(46, 17, 0, 3),
(47, 18, 0, 3),
(48, 19, 0, 3),
(49, 20, 0, 3),
(50, 22, 0, 3),
(51, 23, 0, 3),
(52, 24, 0, 3),
(53, 25, 0, 3),
(54, 26, 0, 3),
(55, 28, 0, 3),
(56, 29, 0, 3),
(57, 30, 0, 3),
(58, 31, 0, 3),
(59, 32, 0, 3),
(60, 34, 0, 3),
(61, 35, 0, 3),
(62, 36, 0, 3),
(63, 37, 0, 3),
(64, 38, 0, 3),
(65, 39, 0, 3),
(66, 40, 0, 3),
(67, 41, 0, 3),
(68, 42, 0, 3),
(69, 43, 0, 3),
(70, 44, 0, 3),
(71, 45, 0, 3),
(72, 46, 0, 3),
(73, 47, 0, 3),
(74, 48, 0, 3),
(75, 49, 0, 3),
(76, 50, 0, 3),
(77, 51, 0, 3),
(78, 52, 0, 3),
(79, 54, 0, 3),
(80, 58, 0, 3),
(81, 59, 0, 3),
(82, 60, NULL, 3),
(83, 62, 0, 3),
(84, 63, 0, 3),
(85, 64, 0, 3),
(86, 65, 0, 3),
(87, 66, 0, 3),
(88, 68, 0, 3),
(89, 69, 0, 3),
(90, 70, 0, 3),
(91, 71, 0, 3),
(92, 75, 0, 3),
(93, 76, 0, 3),
(94, 77, 0, 3),
(95, 78, 0, 3),
(96, 79, 0, 3),
(97, 80, 0, 3),
(98, 82, 0, 3),
(99, 83, 0, 3),
(100, 85, 0, 3),
(101, 86, 0, 3),
(102, 87, 0, 3),
(103, 88, 0, 3),
(104, 89, 0, 3),
(105, 243, 0, 3),
(106, 90, 0, 3),
(107, 91, 0, 3),
(108, 92, 0, 3),
(109, 93, 0, 3),
(110, 94, 0, 3),
(111, 95, 0, 3),
(112, 96, 0, 3),
(113, 98, 0, 3),
(114, 99, 0, 3),
(115, 100, 0, 3),
(116, 101, 0, 3),
(117, 102, 0, 3),
(118, 244, 0, 3),
(119, 104, 0, 3),
(120, 106, 0, 3),
(121, 107, 0, 3),
(122, 245, 0, 3),
(123, 108, 0, 3),
(124, 109, 0, 3),
(125, 110, 0, 3),
(126, 111, 0, 3),
(127, 112, 0, 3),
(128, 113, 0, 3),
(129, 114, 0, 3),
(130, 115, 0, 3),
(131, 116, 0, 3),
(132, 118, 0, 3),
(133, 119, 0, 3),
(134, 120, 0, 3),
(135, 121, 0, 3),
(136, 122, 0, 3),
(137, 125, 0, 3),
(138, 126, 0, 3),
(139, 127, 0, 3),
(140, 128, 0, 3),
(141, 129, 0, 3),
(142, 130, 0, 3),
(143, 131, 0, 3),
(144, 133, 0, 3),
(145, 134, 0, 3),
(146, 135, 0, 3),
(147, 136, 0, 3),
(148, 137, 0, 3),
(149, 138, 0, 3),
(150, 139, 0, 3),
(151, 140, 0, 3),
(152, 141, 0, 3),
(153, 142, 0, 3),
(154, 242, 0, 3),
(155, 143, 0, 3),
(156, 144, 0, 3),
(157, 145, 0, 3),
(158, 146, 0, 3),
(159, 147, 0, 3),
(160, 148, 0, 3),
(161, 149, 0, 3),
(162, 151, 0, 3),
(163, 152, 0, 3),
(164, 153, NULL, 3),
(165, 154, 0, 3),
(166, 155, 0, 3),
(167, 156, 0, 3),
(168, 157, NULL, 3),
(169, 158, 0, 3),
(170, 159, 0, 3),
(171, 161, 0, 3),
(172, 162, 0, 3),
(173, 163, 0, 3),
(174, 241, 0, 3),
(175, 164, 0, 3),
(176, 165, 0, 3),
(177, 166, 0, 3),
(178, 167, 0, 3),
(179, 168, 0, 3),
(180, 169, 0, 3),
(181, 172, 0, 3),
(182, 173, 0, 3),
(183, 174, 0, 3),
(184, 176, 0, 3),
(185, 177, 0, 3),
(186, 178, 0, 3),
(187, 181, 0, 3),
(188, 182, 0, 3),
(189, 183, 0, 3),
(190, 184, 0, 3),
(191, 185, 0, 3),
(192, 236, 0, 3),
(193, 186, 0, 3),
(194, 187, 0, 3),
(195, 188, 0, 3),
(196, 191, 0, 3),
(197, 192, 0, 3),
(198, 193, 0, 3),
(199, 194, 0, 3),
(200, 246, 0, 3),
(201, 196, 0, 3),
(202, 197, 0, 3),
(203, 198, 0, 3),
(204, 199, 0, 3),
(205, 200, 0, 3),
(206, 201, 0, 3),
(207, 202, 0, 3),
(208, 204, 0, 3),
(209, 205, 0, 3),
(210, 206, 0, 3),
(211, 207, 0, 3),
(212, 208, 0, 3),
(213, 209, 0, 3),
(214, 61, 0, 3),
(215, 210, 0, 3),
(216, 211, 0, 3),
(217, 212, 0, 3),
(218, 213, 0, 3),
(219, 214, 0, 3),
(220, 215, 0, 3),
(221, 216, 0, 3),
(222, 217, 0, 3),
(223, 218, 0, 3),
(224, 219, 0, 3),
(225, 220, 0, 3),
(226, 221, 0, 3),
(227, 223, 0, 3),
(228, 224, 0, 3),
(229, 225, 0, 3),
(230, 226, 0, 3),
(231, 227, NULL, 3),
(232, 228, 0, 3),
(233, 229, 0, 3),
(234, 230, 0, 3),
(235, 231, 0, 3),
(236, 232, 0, 3),
(237, 233, 0, 3),
(238, 234, 0, 3),
(239, 235, 0, 3),
(240, 238, 0, 3),
(241, 239, 0, 3);