CREATE TABLE address id SERIAL NOT NULL PRIMARY KEY address1 VARCHAR 6

  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
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
CREATE TABLE address
(
id SERIAL NOT NULL PRIMARY KEY,
address1 VARCHAR(64) NOT NULL,
address2 VARCHAR(64) NULL,
city VARCHAR(16) NOT NULL,
country VARCHAR(16) NOT NULL,
zip INTEGER NOT NULL,
phone VARCHAR(20) NULL
)
;
CREATE TABLE articles
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(20) NULL
)
;
CREATE TABLE client
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(32) NOT NULL,
person VARCHAR(64) NULL,
address_id INTEGER NOT NULL REFERENCES "address" ("id")
)
;
CREATE TABLE dayoff
(
id SERIAL NOT NULL PRIMARY KEY,
start DATE NOT NULL,
finish DATE NOT NULL,
type_id INTEGER NOT NULL REFERENCES "dayoff_type" ("id"),
worker INTEGER NOT NULL REFERENCES "worker" ("id"),
comment VARCHAR(64) NULL
)
;
CREATE TABLE dayoff_type
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(32) NOT NULL
)
;
CREATE TABLE income
(
id SERIAL NOT NULL PRIMARY KEY,
date DATE NOT NULL,
payed INTEGER NOT NULL,
task_id INTEGER NOT NULL REFERNCES "task" ("id")
)
;
CREATE TABLE interview
(
id SERIAL NOT NULL PRIMARY KEY,
surname VARCHAR(20) NULL,
name VARCHAR(20) NULL,
patronym VARCHAR(20) NULL,
address_id INTEGER NOT NULL REFERENCES "address"("id"),
date TIMESTAMP,
worker_id INTEGER NULL REFERENCES "worker" ("id"),
comment VARCHAR(64) NULL,
status INTEGER NOT NULL
)
;
CREATE TABLE price_list
(
id SERIAL NOT NULL PRIMARY KEY,
price DECIMAL(10,2) NOT NULL,
valid_from DATE NOT NULL,
article_id INTEGER NOT NULL REFERENCES "article" ("id")
)
;
CREATE TABLE specialization
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(24) NOT NULL
)
;
CREATE TABLE t_status
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(18) NOT NULL
)
;
CREATE TABLE task
(
id SERIAL NOT NULL PRIMARY KEY,
date_accpeted DATE NOT NULL,
est_date DATE NOT NULL,
worker_id INTEGER NOT NULL REFERENCES "worker" ("id"),
client_id INTEGER NOT NULL REFERENCES "client" ("id"),
article_id INTEGER NOT NULL REFERENCES "article" ("id")
)
;
CREATE TABLE task_status
(
id SERIAL NOT NULL PRIMARY KEY,
start TIMESTAMP NOT NULL,
task_id INTEGER NOT NULL REFERENCES "task" ("id"),
status_id INTEGER NOT NULL REFERENCES "status" ("id")
)
;
CREATE TABLE worker
(
worker_id SERIAL NOT NULL PRIMARY KEY,
surname VARCHAR(20) NULL,
name VARCHAR(20) NULL,
patronym VARCHAR(20) NULL,
hired DATE NOT NULL,
fired DATE NULL,
address_id INTEGER NOT NULL REFERENCES "address" ("id"),
comment VARCHAR(64) NULL
)
;
CREATE TABLE worker_specialization
(
id SERIAL NOT NULL PRIMARY_KEY,
start DATE NOT NULL,
specialization_id INTEGER NOT NULL REFERENCES "specialization" ("id"),
worker_id INTEGER NOT NULL REFERENCES "worker" ("id"),
comment VARCHAR(64) NULL
)
;