CREATE TABLE IF NOT EXISTS `article` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`price` float NOT NULL,
`currency` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`,`currency`),
KEY `fk_article_currencies1` (`currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
INSERT INTO `article` (`id`, `name`, `price`, `currency`) VALUES
(1, 'Euro Artikel teuer', 1, 1),
(2, 'Euro Artikel billig', 100, 1),
(3, 'CHF Artikel billig', 1, 2),
(4, 'CHF Artikel teuer', 100, 2),
(5, 'USD Artikel billig', 1, 3),
(6, 'US Artikel teuer', 100, 3);
CREATE TABLE IF NOT EXISTS `currencies` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`abbrevation` char(3) NOT NULL,
`symbol` char(1) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name_UNIQUE` (`name`),
UNIQUE KEY `abbrevation_UNIQUE` (`abbrevation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
INSERT INTO `currencies` (`id`, `name`, `abbrevation`, `symbol`) VALUES
(1, 'Euro', 'EUR', '€'),
(2, 'Schweizer Franken', 'CHF', NULL),
(3, 'US Dollar', 'USD', '$');
CREATE TABLE IF NOT EXISTS `exchangeRates` (
`from` int(10) unsigned NOT NULL,
`to` int(10) unsigned NOT NULL,
`rate` float DEFAULT NULL,
PRIMARY KEY (`from`,`to`),
KEY `fk_table1_currencies1` (`to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `exchangeRates` (`from`, `to`, `rate`) VALUES
(1, 1, 1),
(1, 2, 0.5),
(1, 3, 0.87),
(2, 1, 2.5),
(2, 2, 1),
(2, 3, 1.4),
(3, 1, 1.27),
(3, 2, 0.3),
(3, 3, 1);
ALTER TABLE `article`
ADD CONSTRAINT `fk_article_currencies1` FOREIGN KEY (`currency`) REFERENCES `currencies` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `exchangeRates`
ADD CONSTRAINT `fk_table1_currencies` FOREIGN KEY (`from`) REFERENCES `currencies` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_table1_currencies1` FOREIGN KEY (`to`) REFERENCES `currencies` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
