SMS/database.dbml at main · aarch64-softdes/SMS · 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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
// Database Schema for Student Management System

Table identities {
  id varchar [pk]
  type varchar
  number varchar
  issued_by varchar
  issued_date date
  expiry_date date
  has_chip boolean
  country varchar
  notes varchar
}

Table addresses {
  id varchar [pk]
  street varchar
  ward varchar
  district varchar
  province varchar
  country varchar
}

Table settings {
  id integer [pk, increment]
  name varchar [unique]
  details text
}

Table faculties {
  id integer [pk, increment]
  name varchar [unique]
  deleted_at date
}

Table programs {
  id integer [pk, increment]
  name varchar [unique]
  deleted_at date
}

Table statuses {
  id integer [pk, increment]
  name varchar [unique]
  deleted_at date
}

Table students {
  id varchar [pk]
  student_id varchar [unique]
  name varchar
  dob date
  gender varchar
  faculty_id integer [ref: > faculties.id]
  school_year integer
  program_id integer [ref: > programs.id]
  email varchar [unique]
  phone varchar(12) [unique]
  status_id integer [ref: > statuses.id]
  permanent_address_id varchar [ref: - addresses.id, unique]
  temporary_address_id varchar [ref: - addresses.id, unique]
  mailing_address_id varchar [ref: - addresses.id, unique]
  identity_id varchar [ref: - identities.id, unique]
  deleted_at date
}

Table status_transitions {
  from_status_id integer [ref: > statuses.id]
  to_status_id integer [ref: > statuses.id]

  indexes {
    (from_status_id, to_status_id) [pk]
  }
}

Table subjects {
  id integer [pk, increment]
  name varchar
  code varchar(50) [unique]
  description text
  is_active boolean [default: true]
  credits integer
  faculty_id integer [ref: > faculties.id]
  deleted_at date
  created_at timestamp [default: `CURRENT_TIMESTAMP`]
}

Table subject_prerequisites {
  subject_id integer [ref: > subjects.id]
  prerequisite_id integer [ref: > subjects.id]

  indexes {
    (subject_id, prerequisite_id) [pk]
  }
}

Table courses {
  id integer [pk, increment]
  code varchar(50)
  year integer
  semester integer
  lecturer varchar
  max_student integer
  room varchar(50)
  schedule varchar(50)
  start_date date
  program_id integer [ref: > programs.id]
  subject_id integer [ref: > subjects.id]

  indexes {
    (code, subject_id) [unique]
  }
}

Table scores {
  id integer [pk, increment]
  grade varchar
  gpa float
}

Table enrollments {
  id integer [pk, increment]
  student_id varchar [ref: > students.id]
  course_id integer [ref: > courses.id]
  score_id integer [ref: - scores.id, unique]

  indexes {
    (student_id, course_id) [unique]
  }
}

Table histories {
  id varchar [pk]
  action_type varchar(50)
  created_at timestamp
  student_id varchar [ref: > students.id]
  course_id integer [ref: > courses.id]
}