-
Notifications
You must be signed in to change notification settings - Fork 0
/
projectmanagment.sql
83 lines (70 loc) · 2.51 KB
/
projectmanagment.sql
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
CREATE TABLE `projects` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`start_date` DATE NOT NULL,
`description` TEXT NOT NULL,
`is_active` BOOLEAN NOT NULL,
`estimated_time` INT,
`team_lead` INT NOT NULL,
`target_audience` VARCHAR(255) NOT NULL,
`end_date` DATE,
`created_at` DATETIME NOT NULL,
`deleted_at` DATETIME,
`estimated_funds` DECIMAL NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(255) NOT NULL,
`email` VARCHAR(255) NOT NULL,
`password` VARCHAR(255) NOT NULL,
`first_name` VARCHAR(255) NOT NULL,
`last_name` VARCHAR(255) NOT NULL,
`Date_of_birth` DATE NOT NULL,
`role` VARCHAR(255) NOT NULL,
`gender` VARCHAR(255) NOT NULL,
`city` VARCHAR(255),
PRIMARY KEY (`id`)
);
CREATE TABLE `project_instances` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`city` VARCHAR(255) NOT NULL,
`address` TEXT,
PRIMARY KEY (`id`)
);
CREATE TABLE `project_to_do_list` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`task` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `project_instance_personnel` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_instance_id` INT NOT NULL,
`user_id` INT NOT NULL,
`role_project` INT NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `funds_received` (
`id` INT NOT NULL AUTO_INCREMENT,
`date` DATE NOT NULL,
`amount(in rs)` DECIMAL NOT NULL,
`project_id` INT NOT NULL,
`source` TEXT,
PRIMARY KEY (`id`)
);
CREATE TABLE `funds_spent` (
`id` INT NOT NULL AUTO_INCREMENT,
`project_id` INT NOT NULL,
`date` DATE NOT NULL,
`amout_alotted` DATE NOT NULL,
PRIMARY KEY (`id`)
);
ALTER TABLE `projects` ADD CONSTRAINT `projects_fk0` FOREIGN KEY (`team_lead`) REFERENCES `users`(`id`);
ALTER TABLE `project_instances` ADD CONSTRAINT `project_instances_fk0` FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`);
ALTER TABLE `project_to_do_list` ADD CONSTRAINT `project_to_do_list_fk0` FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`);
ALTER TABLE `project_instance_personnel` ADD CONSTRAINT `project_instance_personnel_fk0` FOREIGN KEY (`project_instance_id`) REFERENCES `project_instances`(`id`);
ALTER TABLE `project_instance_personnel` ADD CONSTRAINT `project_instance_personnel_fk1` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`);
ALTER TABLE `funds_received` ADD CONSTRAINT `funds_received_fk0` FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`);
ALTER TABLE `funds_spent` ADD CONSTRAINT `funds_spent_fk0` FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`);