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