---
# System prepended metadata

title: Database design
tags: [Planning]

---

# Database design

Phase 1: 完成民數系統
Phase 2: 加入族群統計

## 民數系統

### Users

| id (PK) | username | email  | birthday | mobile_number | level  | role   |
| ------- | -------- | ------ | -------- | ------------- | ------ | ------ |
| Integer | String   | String | String   | String        | String | String |

### Organization_categories

| id (PK) | category_name |
| ------- | ------------- |
| Integer | String        |

### Organization_units

| id (PK) | name   | category_id (FK) | parent_unit_id (FK) | leader_id (FK) | created_at |
| ------- | ------ | ---------------- | ------------------- | -------------- | ---------- |
| Integer | String | Integer          | Integer             | Integer        | DateTime   |

### user_organizations

| user_id (FK) | unit_id (FK) | joined_at |
| ------------ | ------------ | --------- |
| Integer      | Integer      | DateTime  |

> 只記錄最低層級的單位和使用者的關聯性，以減少資料量
> 如果真的碰到效能問題，再進行調整

### Numbers attendance

| user_id | meeting_type | created_at |
| ------- | ------------ | ---------- |
| Integer | String       | DateTime   |


### 族群

### 資料表關係圖

```graphviz
digraph G {
    graph [splines=ortho];
    rankdir=LR;
    node [shape=record, fontname="Arial", fontsize=10];
    edge [fontname="Arial", fontsize=8];
    
    // 設定節點樣式
    members [label="{members|+ id (PK): varchar\l+ username: varchar\l+ birthday: date\l+ email: varchar\l+ mobile_number: varchar\l}"];
    
    organization_levels [label="{organization_levels|+ id (PK): int\l+ level_name: varchar\l}"];
    
    organization_units [label="{organization_units|+ id (PK): varchar\l+ name: varchar\l+ level_id (FK): int\l+ parent_unit_id (FK): varchar\l+ leader_id (FK): varchar\l+ created_at: timestamp\l}"];
    
    member_organizations [label="{member_organizations|+ member_id (PK, FK): varchar\l+ unit_id (PK, FK): varchar\l+ joined_at: timestamp\l}"];
    
    // 設定關係
    edge [dir=both, arrowhead=crow, arrowtail=none];
    
    members -> member_organizations [label="1:N"];
    members -> organization_units [label="1:N\n(as leader)"];
    organization_levels -> organization_units [label="1:N"];
    organization_units -> organization_units [label="1:N\n(parent-child)"];
    
    // 明確指定 organization_units 到 member_organizations 的邊
    organization_units -> member_organizations [label="1:N", constraint=true];
    
    // 排版優化
    {rank=same; members organization_levels}
    {rank=same; member_organizations organization_units}
}
```