-
Notifications
You must be signed in to change notification settings - Fork 0
/
sqerl.sql
40 lines (36 loc) · 1.49 KB
/
sqerl.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
DROP TABLE IF EXISTS trades;
DROP TABLE IF EXISTS currencies;
DROP TABLE IF EXISTS origins;
DROP TABLE IF EXISTS traders;
CREATE TABLE traders (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ext_id INT UNSIGNED NOT NULL,
PRIMARY KEY( id ),
UNIQUE( ext_id ) ) ENGINE=InnoDB CHARSET=utf8;
CREATE TABLE origins (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR( 20 ) NOT NULL,
PRIMARY KEY( id ),
UNIQUE( name ) ) ENGINE=InnoDB CHARSET=utf8;
CREATE TABLE currencies (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR( 20 ) NOT NULL,
PRIMARY KEY( id ),
UNIQUE( name ) ) ENGINE=InnoDB CHARSET=utf8;
CREATE TABLE trades (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
trader_id INT UNSIGNED NOT NULL,
origin_id INT UNSIGNED NOT NULL,
from_currency_id INT UNSIGNED NOT NULL,
to_currency_id INT UNSIGNED NOT NULL,
from_amount DECIMAL( 20, 5 ) NOT NULL,
to_amount DECIMAL( 20, 5 ) NOT NULL,
rate DECIMAL( 20, 5 ) NOT NULL,
time TIMESTAMP NOT NULL,
PRIMARY KEY( id ),
INDEX( time ),
INDEX( rate ),
FOREIGN KEY( trader_id ) REFERENCES traders( id ) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY( origin_id ) REFERENCES origins( id ) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY( from_currency_id ) REFERENCES currencies( id ) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY( to_currency_id ) REFERENCES currencies( id ) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB CHARSET=utf8;