科學數據設計。具有數百列的數據表或具有通用值列和數百行 (EAV) 的數據表？

我正在努力改善我最近開始工作的生物醫學科學實驗室的數據儲存。現有的工作流程非常糟糕，涉及許多不同格式的 Excel 工作表，這些工作表都通過複製粘貼和錯誤宏的過程進行匯總。

我的目的是創建一個簡單的 python 腳本，它將實驗的所有數據聚合到 SQLite 數據庫中，然後生成必要的 CSV/XLSX 輸出。

我的問題是，對於我們的單次試驗，我們最終會在大約 10 個不同的時間點記錄大約 100 個變數。我最初的衝動是創建一個value和variable表：

CREATE TABLE value (val_id INTEGER PRIMARY KEY, 
                   value TEXT, 
                   var_id INTEGER,
                   event_id INTEGER,
                   exp_id INTEGER,
                   FOREIGN KEY (var_id) REFERENCES variable(var_id),
                   FOREIGN KEY (event_id) REFERENCES event(event_id),
                   FOREIGN KEY (exp_id) REFERENCES experiemnt(exp_id)
);

CREATE TABLE variable (var_id INTEGER PRIMARY KEY,
                     var_name TEXT,
                      var_type TEXT
);

value:
val_id | value | var_id | ...
0      | 10    | 0
1      | "ROSC"| 5

variable:
var_id | var_name | var_type
0      | Pressure | DECIMAL
...
5      | Outcome  | TEXT

但這感覺不對，我有一種預感，“正確”的方法是擁有一個包含數百個列的單個數據表，否則這些列將在variable表中進行描述，因為這樣可以更輕鬆地進行類型檢查（是的，我知道 SQLite 不這樣做，但原則上）。

任何有關如何解決此問題的見解將不勝感激。

您所描述的是大多數數據庫專業人員都會執行的 EAV（實體屬性值）模型。它也被諷刺地稱為 OTLT（一個真正的查找表），是一個經典的新手錯誤。你的預感是對的！

這裡（和這裡）是 Joe Celko（一位資深的 SQL 程序員，是/曾經是 SQL 標準委員會的成員。他用“EAV of destroy”來命名他的文章這一事實應該給你一個線索：- ）。Celko 也將此稱為大規模統一程式碼密鑰。

首字母縮略詞是 MUCK 絕非巧合！:-)

以這種方式儲存數據會破壞關係數據庫的許多優點，例如 DRI（聲明性引用完整性）、CHECK 約束和 DEFAULT 值。

請創建一個包含 100 個欄位和 10 行的表 - 如果這是您的數據需要的，那麼就這樣做。也許其他幾個帶有實驗 ID、日期時間和實驗者 ID 的欄位也有用？這樣，您就可以在給定的時間段內執行各種其他聚合分析 - 基本上是對所有數據進行切片和切塊。

<個人意見> 如果你還沒有選擇你的數據庫並且樂於使用F/LOSS，那麼我可以推薦PostgreSQL——它的SQL方言是迄今為止最豐富的開源數據庫（我與項目無關） . 在這裡查看如何在 SQLite 中強制執行數據類型，但 PostgreSQL 提供的不僅僅是 SQLite - 例如它是多使用者的，無需跳過箍來強制執行數據類型。</個人意見>

$$ EDIT1 $$ 再說一句，為了完整起見，只有一個重要的系統使用 EAV 模型——那就是 Magento ( 1 , 2 )。它的主要利基市場是時尚行業，其中 EAV 模型可能適用於稀疏的桌子（時尚產品往往有多種顏色、款式、尺寸……）。它很流行（1、2），但 MySQL 也是如此，它在許多方面都不如 PostgreSQL、Firebird 和（除了多使用者功能）SQLite。

引用自：https://dba.stackexchange.com/questions/104694