---
# System prepended metadata

title: DB schema
tags: [backend]

---

---
tags: backend
---
# DB schema
### user
| attribute       | Type                            | Example                              | Note                                             |
|:--------------- |:------------------------------- |:------------------------------------ |:------------------------------------------------ |
| user_id         | VARCHAR(36)</br>**Primary Key** | 842f638c-4f58-11ed-8453-0242ac1b0003 | uuid                                             |
| student_id      | VARCHAR(256)                    | u11016025                            | Index1                                           |
| enabled         | TINYINT(1)                      | 1                                    | 0: 停權, 1: 授權                                 |
| last_updated_at | TIMESTAMP                       | 2022-10-29 02:48:31                  | Default: current time</br>onupdate: current time |
| last_updated_by | VARCHAR(256)                    | Brian                                |                                                  |
| created_at      | TIMESTAMP                       | 2022-10-19 00:00:00                  | Default: current time                            |
| token           | VARCHAR(512)                    | (token)                              |                                                  |

### leave_record
| attribute       | Type                            | Example                              | Note                                             |
|:--------------- |:------------------------------- |:------------------------------------ |:------------------------------------------------ |
| record_id       | VARCHAR(36)</br>**Primary Key** | 9a4e7638-e47f-41e7-b09b-ef6aad43276e | uuid                                             |
| user_id         | VARCHAR(36)</br>**Foreign Key** | 842f638c-4f58-11ed-8453-0242ac1b0003 |                                                  |
| date            | TIMESTAMP                       |                                      |                                                  |
| weekday         | SMALLINT                        | 1                                    |                                                  |
| session         | VARCHAR(256)                    | 1, 2,3                               |                                                  |
| leave_type      | SMALLINT                        | 22                                   |                                                  |
| reason          | VARCHAR(500)                    | 早上肚子痛，請病假                   |                                                  |
| status          | SMALLINT                        | 1                                    | 0:審核, 1:通過, 2:拒絕                           |
| last_updated_at | TIMESTAMP                       | 2022-10-29 02:48:31                  | Default: current time</br>onupdate: current time |
| send_time       | TIMESTAMP                       | 2022-10-19 00:00:00                  | Default: current time                            |
| course_id       | VARCHAR(36)<br>**foreign key** | 9a4e7638-e47f-41e7-b09b-ef6aad43276e |                                                  |
| last_check_at   | TIMESTAMP                       | 2022-10-30 00:00:00                  | 上一次與校務系統核對時間                         |

### course
| attribute          | Type                            | Example                              | Note                                             |
|:------------------ |:------------------------------- |:------------------------------------ |:------------------------------------------------ |
| course_id          | VARCHAR(36)</br>**Primary Key** | 9a4e7638-e47f-41e7-b09b-ef6aad43276e | uuid                                             |
| year               | SMALLINT                        | 111                                  |                                                  |
| semester           | SMALLINT                        | 1                                    |                                                  |
| code               | VARCHAR(256)                    | 1234                                 | Index1                                           |
| year_semester_code | VARCHAR(256)                    | 11111234                             |  unique                                                |
| name               | VARCHAR(256)                    |                                      | Index2                                           |
| weekday            | SMALLINT                        | 1                                    |                                                  |
| session           | VARCHAR(256)                    | 1, 2, 3                              |                                                  |
| teacher            | VARCHAR(256)                    |                                      |                                                  |
| last_updated_at    | TIMESTAMP                       | 2022-10-19 02:48:31                  | Default: current time</br>onupdate: current time |

### login_record
| attribute       | Type                        | Example                              | Note                                             |
|:--------------- |:--------------------------- |:------------------------------------ |:------------------------------------------------ |
| login_record_id | VARCHAR(36)</br>**Primary Key** | 9a4e7638-e47f-41e7-b09b-ef6aad43276e   | uuid                                         |
| user_id         | VARCHAR(36)</br>**Foreign Key** | 842f638c-4f58-11ed-8453-0242ac1b0003  | uuid                                         |
| ip              | VARCHAR(256)                |                                      | Index2                                             |
| login_time      | TIMESTAMP                   |                                      |                                                   |
| status          | SMALLINT                    |   2                                  | error code                                         |
| user_agent      | VARCHAR(512)                |                                      |                                                   |


![](https://i.imgur.com/PPZLREO.png)
