Project

General

Profile

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
);
20

    
21
INSERT INTO groups(id, name, type) VALUES ('UNKNOWN', 'UNKNOWN', 'UNKNOWN');
22

    
23
CREATE VIEW affiliations_view AS SELECT
24
	p.id                                                               as pid,
25
	TRIM(CONCAT(p.surname, ", ", p.name, " ", COALESCE(p.suffix, ''))) as pfullname,
26
	p.fullname                                                         as pname,  //TODO DA TOGLIERE
27
	g.id                                                               as gid,
28
	g.name                                                             as gname,
29
	g.type                                                             as gtype,
30
	a.year                                                             as year
31
FROM 
32
	persons p 
33
	LEFT OUTER JOIN affiliations a ON (p.id = a.pid)
34
	LEFT OUTER JOIN       groups g ON (a.gid = g.id);
(1-1/2)