CapacityPlannerBackEnd/db.sql at develop · ksafavi/CapacityPlannerBackEnd · GitHub

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
create table Location
(
    id   int auto_increment
        primary key,
    name varchar(1000) null
);

create table LocationHoliday
(
    id         int auto_increment
        primary key,
    locationId int           null,
    name       varchar(1000) not null,
    date       datetime      not null,
    constraint LocationHoliday_Location_id_fk
        foreign key (locationId) references Location (id)
            on delete cascade
);

create table Person
(
    id         int auto_increment
        primary key,
    locationId int           not null,
    firstName  varchar(1000) not null,
    lastName   varchar(1000) not null,
    constraint Person_Location_id_fk
        foreign key (locationId) references Location (id)
            on delete cascade
);

create table PersonVacation
(
    id            int auto_increment
        primary key,
    personId      int      not null,
    date          datetime not null,
    fractionOfDay float    not null,
    constraint PersonVacation_Person_id_fk
        foreign key (personId) references Person (id)
            on delete cascade
);

create table ProgramIncrement
(
    id   int auto_increment
        primary key,
    name varchar(1000) not null
);

create table Iteration
(
    id                 int auto_increment
        primary key,
    programIncrementId int           not null,
    name               varchar(1000) not null,
    startDate          datetime      not null,
    lengthInDays       int           not null,
    points             int           not null,
    constraint Iteration_ProgramIncrement_id_fk
        foreign key (programIncrementId) references ProgramIncrement (id)
            on delete cascade
);

create table Team
(
    id   int auto_increment
        primary key,
    name varchar(1000) not null
);

create table PersonTeam
(
    id         int auto_increment
        primary key,
    personId   int           not null,
    teamId     int           not null,
    role       varchar(1000) not null,
    percentage int           not null,
    constraint PersonTeam_pk
        unique (personId, teamId),
    constraint PersonTeam_Person_id_fk
        foreign key (personId) references Person (id)
            on delete cascade,
    constraint PersonTeam_Team_id_fk
        foreign key (teamId) references Team (id)
            on delete cascade
);