-
Notifications
You must be signed in to change notification settings - Fork 0
/
MYSQLTable.txt
111 lines (87 loc) · 4.55 KB
/
MYSQLTable.txt
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
89
90
91
92
93
94
95
96
97
98
99
100
MGNREGA
create table GramPanchayatMember(
gpmID int primary key auto_increment,
gpmName varchar(20) not null,
gpmAadhar varchar(12) unique not null,
gmDob date not null,
gemGender varchar(6) not null,
gpmEmail varchar(20) unique not null,
gpmPassword varchar(20) not null,
gpName varchar(20) unique not null,
district varchar(20) not null,
state varchar(20) not null,
is_delete boolean not null default(false)
);
create table project(
proID int primary key auto_increment,
proName varchar(20) not null,
proStrDate date not null,
proEndDate date not null,
gpmID int,
nOfWorkes int,
wagesParDay int not null,
is_delete boolean not null default(false),
foreign key (gpmID) references GramPanchayatMember(gpmID)
);
create table workers(
wID int primary key auto_increment,
wName varchar(20) not null,
WAadhar varchar(12) unique not null,
wDob date not null,
Wender varchar(10) not null,
gpName varchar(20) not null,
gpmID int not null,
proID int,
workStrDate date,
district varchar(20) not null,
state varchar (20) not null,
is_delete boolean not null default(false),
foreign key (proID) references project(proID),
foreign key (gpmID) references GramPanchayatMember(gpmID)
);
GramPanchayatMember
+-------------+-------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------------------+
| gpmID | int | NO | PRI | NULL | auto_increment |
| gpmName | varchar(20) | NO | | NULL | |
| gpmAadhar | varchar(12) | NO | UNI | NULL | |
| gmDob | date | NO | | NULL | |
| gemGender | varchar(6) | NO | | NULL | |
| gpmEmail | varchar(20) | NO | UNI | NULL | |
| gpmPassword | varchar(20) | NO | | NULL | |
| gpName | varchar(20) | NO | UNI | NULL | |
| district | varchar(20) | NO | | NULL | |
| state | varchar(20) | NO | | NULL | |
| is_delete | tinyint(1) | NO | | false | DEFAULT_GENERATED |
+-------------+-------------+------+-----+---------+-------------------+
project
+-------------+-------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------------------+
| proID | int | NO | PRI | NULL | auto_increment |
| proName | varchar(20) | NO | | NULL | |
| proStrDate | date | NO | | NULL | |
| proEndDate | date | NO | | NULL | |
| gpmID | int | YES | MUL | NULL | |
| nOfWorkes | int | YES | | NULL | |
| wagesParDay | int | NO | | NULL | |
| is_delete | tinyint(1) | NO | | false | DEFAULT_GENERATED |
+-------------+-------------+------+-----+---------+-------------------+
workers
+-------------+-------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------------------+
| wID | int | NO | PRI | NULL | auto_increment |
| wName | varchar(20) | NO | | NULL | |
| WAadhar | varchar(12) | NO | UNI | NULL | |
| wDob | date | NO | | NULL | |
| Wender | varchar(10) | NO | | NULL | |
| gpName | varchar(20) | NO | | NULL | |
| gpmID | int | NO | MUL | NULL | |
| proID | int | YES | MUL | NULL | |
| workStrDate | date | YES | | NULL | |
| district | varchar(20) | NO | | NULL | |
| state | varchar(20) | NO | | NULL | |
| is_delete | tinyint(1) | NO | | false | DEFAULT_GENERATED |
+-------------+-------------+------+-----+---------+-------------------+