# 可由使用者 “後定義” 的資料欄位在關聯式資料庫的 Schema 實作設計

> **RDB schema design of user after defined data columns**

* 共筆版本：https://hackmd.io/@effective-crud/User-After-Defined-RDB-Columns
* Git repo: https://github.com/Effective-CRUD/Continues-Protocol-Development

###### tags `關聯式資料庫`, `技術選型`

---

## 概觀

### 問題背景

所謂的 “後定義” 資料欄位，就是每筆紀錄的欄位數量是可變、不固定的。使用者可以依照需求，在每筆紀錄中儲存所需的欄位和資料。當我們在規劃一個支援使用者後設資料、後定義欄位的資料管理系統，與資料庫架構時，通常會選用 NoSQL 資料庫。

又或是在人力、時程 ... 資源有限的情況下，折衷使用原有的關聯式資料庫 (RDB)，和使用其 NoSQL 型別，以及一個特殊的欄位來處理這些 user defined 紀錄。 ([例如 PostgreSQL 的 JSON 型別](https://docs.postgresql.tw/the-sql-language/data-types/json-types))。

但是在某些條件下，例如系統複雜到改不動的時候，就必須沿用關聯式資料庫，與**不能亂改**的資料型別來實作 (不能亂改 = 應用層不能改、不想改)，就會遇到一種不得不繼續使用關連式資料庫，但是又得用各種反模式、欠下技術債來開發的情況。

### 技術債？反模式？

技術債指的是，為了實現新需求，和相容原有架構，因此 schema 得繼續採用 1NF (一階正規化) -- 大資料表的設計。而這個資料表的設計，看起來就像是在用試算表儲存資料一樣；而且大資料表還有個問題，就是資料表最後可能會長出 3000 個欄位，但是大部分的紀錄，又只有用到 20% 的欄位。

反模式指的則是，我們不得不使用關聯式資料庫，但是又不把它當成關聯式資料庫來使用。而是把它當成一個支援 SQL 語法的 “試算表”。

### 如何在 RDB 的 "精神" 上，設計 “使用者後定義” 的資料表

對於有經驗的工程師與 DBA 來說，這個問題並不複雜。我們只要將各種型別的**欄位**，通通規劃到獨立的資料表就好了，然後再關聯到所屬的**紀錄**資料表；紀錄資料表則會關連到主資料表。我們姑且將這個方案稱為小資料表。

### 技術與風險考量

:::warning
**大資料表**、**小資料表**並不是嚴謹的工程用詞，這邊僅用於解釋本文的使用案例，與遇到的技術 (債) 問題。
:::

當然了，沒有最好的萬用解決方法，只有適合的架構。原有的大資料表方案 -- 把資料庫當成試算表來存資料，真的有什麼不對嗎？正規化為小資料真的有比較好？比較好的定義是什麼？

這個問題，我們可以從系統資源需求、服務類型來交叉比較，做選擇。

|需求 |大資料表 |小資料表 |
|:--------|:--------------|:----------------|
|I/O bound|**讀取**資料量大 |**寫入**時間久 |
|CPU bound|**無用資料**多 |**搜尋**時間久 |
|分析型服務 |**後計算**成本高|**前處理**成本高 |
|交易型服務 |**一致性**維護成本高|**Transaction** 成本高|

---

## 使用案例與 Schema 設計

:::info
由於本文以 Markdown 撰寫，而且尚未找到好用的 ER model *繪圖*方式，因此以下先用 SQL 語法說明 schema 的設計。
:::

### 使用案例：可蒐集資料欄位未知的感測器紀錄系統

我們先設計一個簡單的使用案例，假設使用者有某種感測器模組，需要我們幫忙記錄感測資料。這個感測器特別的地方是，它可以擴充、開關感測功能，也就是說每個感測器回傳的資料種類，需要的欄位數量都是可變的，也可以由使用者定義。

### *感測器*與*感測器紀錄*主表設計

首先，每個感測器需要兩個資料表，一個是**感測器基本資料** (``sensor``)，另一個則是**感測器資料紀錄**的資料表 (``sensor_row``)。``sensor`` 資料表設計如下。

CREATE TABLE sensors
(
   name VARCHAR(36) NOT NULL, -- Display name or UUID
                              -- (32 hex digits + 4 dashes)
   id SERIAL PRIMARY KEY,
   UNIQUE(name),
   
   created_at TIMESTAMP NOT NULL
      DEFAULT CURRENT_TIMESTAMP, 
   updated_at TIMESTAMP DEFAULT NULL
);

``sensor_row`` 資料表的部分，如果這個需求的感測器資料欄位是固定的，那我們也只需要再一個 ``sensor_row`` 資料表就好了。但是在這個例子中，``sensor_row`` 只是用來關連感測紀錄 n 筆欄位的主表。

CREATE TABLE sensor_rows (
   sensor INT NOT NULL        -- 欄位定義紀錄屬於哪個 sensor
      REFERENCES sensors(id)
      ON DELETE CASCADE,
   UNIQUE(id, created_at),    -- 每個 sensor 在單位時間內，
                              -- 只能有一筆紀錄，避免 AP 層、
                              -- 手動亂改造成資料不一致
 
   id SERIAL PRIMARY KEY,
   created_at TIMESTAMP NOT NULL
      DEFAULT CURRENT_TIMESTAMP, 
   updated_at TIMESTAMP DEFAULT NULL
);

:::success
關聯圖：

sensor <-(belongs to)- sensor_row

:::

### *資料欄位*資料表設計

有了感測紀錄主表後，我們就需要實際儲存感測資料，而且資料欄位數量未知的資料表，來保存每個感測紀錄的 n 個欄位資料。

此外資料欄位有不同的資料型別，因此每種型別的欄位，也需要自己的資料表，用來儲存感測資料，並關聯到感測紀錄主表。

我們先從整數資料欄位開始。

CREATE TABLE sensor_row_int_columns (
   sensor_rows INT NOT NULL   -- 欄位定義紀錄屬於哪筆 "紀錄"
      REFERENCES sensor_rows(id)
         ON DELETE CASCADE,
   record INT NOT NULL,       -- 實際紀錄的數值；為了日後搜尋的
                              -- 效率問題，不允許為 null
   id SERIAL PRIMARY KEY,
      created_at TIMESTAMP NOT NULL
         DEFAULT CURRENT_TIMESTAMP, 
   updated_at TIMESTAMP DEFAULT NULL
);

接著就是依照需求，完成 ``float``、``varchar`` ... 型別的*資料欄位*資料表了。

:::success
關聯圖：

           sensor
             ^
             |
        (belongs to)
             |
         sensor_row 
             ^
             |
        (belongs to)
           /   \
          /     \
 sensor_row_    sensor_row_
   int_columns    *_columns

:::

---

## 還少了什麼？

### 可以想像得到的需求

**後設資料欄位的後設資料**

如何實作資料欄位資料表 (``sensor_row_*_columns``) 的欄位屬性 (名稱、功能定義、display name ...)。

**如何定義後設紀錄欄位的 *schema***

如何設計感測紀錄主資料表 (``sensor_rows``) 的預設欄位格式，例如要有哪些資料欄位、怎麼檢查資料是否合法。資料欄位在感測紀錄中的順序定義 (order) 問題，在不同型別的資料欄位 (資料表)，如何處理在感測紀錄中的順序。

### 別想了，勇敢面對真實世界吧

以上的問題，我們可依照需求，在 DB 層另外再設計一個 *attribute*、*metadata* 資料表，用**後設資料的後設資料**，去描述資料欄為、感測紀錄資料表的屬性。當然也可以透過應用層、第三方服務來實作。總之就是回到那句老話：看需求。

譬如在本文的案例中，這個現有系統的任務是要蒐集資料欄位可後定義、數量未知的感測器紀錄。因此他的主要任務就是把這些紀錄好好蒐集下來，並且可以讓使用者增減需要的欄位。但是後續有**想像得到的問題**的需求，其實就表示需求已經超過這個系統的邊界了。即使在現有的架構上實作對應的功能，也只會讓系統愈來愈複雜。

所以這時候就要**勇敢**的跨出單一系統 (或是 App) 的思維，捨棄熟悉的**高耦合、低內聚**反模式、**一大坨**的開發方式了。這部分會陸續在 *Effective CURD* 系列整理出來。

### 最後加映 -- *後設資料的後設資料*資料表

最後，關於前面**後設資料欄位的後設資料**的問題，我們需要再新增一個整數*資料欄位*資料表的後設資料表 (``sensor_row_int_column_metadata``)：

CREATE TABLE sensor_row_int_column_metadata (
   name VARCHAR(36) NOT NULL, -- 由 AP 層定義、紀錄欄位的屬性，
                              -- 建議用 UUID
                              -- (32 hex digits + 4 dashes)
   UNIQUE(name),
   id SERIAL PRIMARY KEY,
   created_at TIMESTAMP NOT NULL
      DEFAULT CURRENT_TIMESTAMP, 
   updated_at TIMESTAMP DEFAULT NULL
);

並且修改原有的*資料欄位*資料表 (``sensor_row_int_columns``)：

ALTER TABLE sensor_row_int_columns
-- 新增欄位的屬性，"後設資料的後設資料"，允許為 NULL
ADD COLUMN
   metadata INT DEFAULT NULL 
      REFERENCES sensor_row_int_column_metadata(id)
      ON DELETE SET NULL,
-- 每筆紀錄只允許有一筆同類型的資料欄位，
-- metadata，若為 NULL 則不限
ADD UNIQUE(sensor_rows, metadata)                   
;

:::success
關聯圖：

             sensor
               ^
               |
          (belongs to)
               |
           sensor_row 
               ^
               |
          (belongs to)
               |
     sensor_row_int_columns
               |
           (has one)
               |
               V
 sensor_row_int_column_metadata

:::