|
1
|
CREATE TABLE persons (
|
|
2
|
id varchar(32) PRIMARY KEY,
|
|
3
|
name varchar(256) NOT NULL,
|
|
4
|
surname varchar(256) NOT NULL,
|
|
5
|
suffix varchar(256)
|
|
6
|
);
|
|
7
|
|
|
8
|
CREATE TABLE groups (
|
|
9
|
id varchar(16) PRIMARY KEY,
|
|
10
|
name varchar(256) NOT NULL,
|
|
11
|
type varchar(32)
|
|
12
|
);
|
|
13
|
|
|
14
|
CREATE TABLE affiliations (
|
|
15
|
pid varchar(32) REFERENCES persons(id),
|
|
16
|
gid varchar(16) REFERENCES groups(id),
|
|
17
|
year int,
|
|
18
|
PRIMARY KEY (pid, gid, year),
|
|
19
|
FOREIGN KEY (pid) REFERENCES persons(id),
|
|
20
|
FOREIGN KEY (gid) REFERENCES groups(id)
|
|
21
|
);
|
|
22
|
|
|
23
|
INSERT INTO groups(id, name, type) VALUES ('UNKNOWN', 'UNKNOWN', 'UNKNOWN');
|
|
24
|
|
|
25
|
CREATE VIEW affiliations_view AS SELECT
|
|
26
|
p.id as pid,
|
|
27
|
TRIM(CONCAT(p.surname, ", ", p.name, " ", COALESCE(p.suffix, ''))) as pfullname,
|
|
28
|
g.id as gid,
|
|
29
|
g.name as gname,
|
|
30
|
g.type as gtype,
|
|
31
|
a.year as year
|
|
32
|
FROM
|
|
33
|
persons p
|
|
34
|
JOIN affiliations a ON (p.id = a.pid)
|
|
35
|
JOIN groups g ON (a.gid = g.id);
|