一對多列表的最佳方法 - 如何儲存/訪問一些相關的計數資訊？

我有一張桌子，上面有人，還有一張桌子，上面有互動、筆記和任務，所有這些都與人有關。我需要快速訪問：

• 上次互動日期。
• 過去 1、6、12 個月的互動次數。
• 音符數量（總計）
• 任務數

所有這些資訊都用於對列表/表格中的突出顯示進行排序。當使用者選擇一個人時，可以從數據庫中請求所有相關資訊，問題是如何從數據庫中獲取所有人的這些數據？

版本 1：子查詢並添加表中每個人的資訊。表增長時可能會很密集。

版本 2：將此資訊儲存在人員表中，在創建/修改新資訊時更新它們。這違反了“真實的單一來源”原則，並且可能容易出錯。

有任何想法嗎？

如果沒有具體的數據庫系統、版本和實現（如果適用）、您的表模式、有多少數據、預期的數據以及一般增長模式等細節，很難推荐一個選項而不是另一個選項。

看起來您正在嘗試對您的數據進行標準類型的OLAP查詢，在這種情況下，這兩個選項都是有效的（在一定程度上）。大多數現代RDBMS可以毫不眨眼地處理數百萬行的表的OLAP類型查詢。因此，只要堅持適當的架構和索引，選項 1 就足以解決某一點。例如（這是了解您所使用的數據庫系統等細節很重要的地方），Microsoft SQL Server 提供列儲存索引，可以極大地提高OLAP的性能通過以有助於改進聚合查詢的列格式有效地壓縮數據來鍵入查詢。YMMV 關於這些類型的功能取決於您使用的數據庫系統。

或者，當您的正常OLTP架構數據庫無法處理的數據過多時，將數據匯總和其他OLAP類型查詢的答案預先儲存在數據立方體中（此處鬆散地使用該術語）會使感覺並通過實時過程（例如通過觸發器）或以業務需求可接受的速率定期計算來完成，並且被認為是在任何時間點處於任何狀態的*唯一事實來源。*但我仍然會遵循某種程度的規範化，而不是將這些資訊直接person儲存在表中，而是儲存在引用該person表的表中（通過儲存person_id例如）。

您有 4 個實體 - 因此 4 個表似乎是個好主意。

• 人（或患者…… - 我將使用它，因為這是我熟悉的場景）。
• 相互作用
• 筆記
• 任務

一個人不會改變 - 他們的名字可能，甚至他們的性別，但他們過去的歷史是不可變的 - 因此 person 表是一個明顯的Single Point of Truth。

數據庫規範化背後的整個基本原理是**“減少數據冗餘並提高數據完整性”**。Wiki 很棒，但不能替代主要來源 - 閱讀規範化並至少讓您的數據庫進入第 3 範式！

另外，請記住，數據庫模式設計是一個迭代過程，並且這裡只有一個問題，我不可能知道您所有的法律、道德和監管限制，所以我會給您一個基本的起點，但是我（或甚至這裡的任何海報）都無法為您提供“開箱即用”的解決方案 - 如果您需要，請購買一些軟體 - 或查看該領域可用的一些/許多F/LOSS 解決方案。

另一個建議是永遠不要（盡可能地）允許使用者輸入自由文本 - 請參閱interaction_type 表作為如何在數據庫中強制執行此操作的範例。您的數據庫伺服器是您保護數據的最後堡壘 - 將盡可能多的約束放入模式 DDL 中 - 這將防止最終使用者（甚至開發人員）破壞您的系統。

我已經使用 PostgreSQL 作為我的小提琴（這裡）——如果你正在考慮使用 F/LOSS 數據庫伺服器，那麼我強烈建議你選擇它而不是任何其他開源產品——它是迄今為止最穩定和符合標準的，而且在許多領域（JSON、GIS、正則表達式…）中具有比其他任何功能更多（和/或更好）的功能。

所以，我將從這樣的基本骨架開始：

• 人：

CREATE TABLE person
(
 person_id INTEGER GENERATED ALWAYS AS IDENTITY,
 p_f_name TEXT NOT NULL,
 p_l_name TEXT NOT NULL,
 p_is_male BOOLEAN,
 p_addr_1 TEXT NOT NULL
 -- 
 -- more fields
 --
);

• Interaction_type - 如何保持對數據輸入的控制！

CREATE TABLE interaction_type
(
 interaction_type_id SMALLINT GENERATED ALWAYS AS IDENTITY,
 i_t_name TEXT NOT NULL,
 i_t_desc TEXT NOT NULL,
 CONSTRAINT interaction_type_pk PRIMARY KEY (interaction_type_id),
 CONSTRAINT i_t_name_lt_50 CHECK (LENGTH(i_t_name) < 25)
);

出於展示目的填充它：

INSERT INTO interaction_type (i_t_name, i_t_desc)
VALUES
('Appointment', 'Scheduled appointment'), ('Emergency', 'Emergency appointment'),
('Walk in', 'Walk in appointment'), ('Home', 'Scheduled home visit'),
('Phone A', 'Scheduled telephone appointment'), 
('Phone U', 'Unscheduled telephone appointment'); -- &c.

懷著世界上最好的意願，人們會犯錯誤——錯別字、加空格……疲勞、分心……——通過限制下面互動表中的interaction_type，您可以準確地進行分析，而不必投入無窮無盡的數量的 OR 條件以考慮錯誤！

然後：

• 相互作用：

CREATE TABLE interaction
(
 interaction_id INTEGER GENERATED ALWAYS AS IDENTITY,
 i_person_id INTEGER NOT NULL,
 i_date_time TIMESTAMP NOT NULL,
 i_type SMALLINT NOT NULL,
 CONSTRAINT interaction_pk PRIMARY KEY (interaction_id),
 CONSTRAINT i_type_fk FOREIGN KEY (i_type) 
   REFERENCES interaction_type (interaction_type_id)
);

• 筆記：

CREATE TABLE note  -- assumption - a note is always accompanied by an event
(                  -- change as required   
 note_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
 note_i_id INTEGER NOT NULL,
 note_text TEXT NOT NULL,
 CONSTRAINT note_pk PRIMARY KEY (note_id),
 CONSTRAINT note_i_fk FOREIGN KEY (note_i_id) REFERENCES interaction (interaction_id)
);

• 任務：

CREATE TABLE task
(
 task_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
 task_type TEXT NOT NULL,
 task_name TEXT NOT NULL, -- could have a table "procedure" - 'X-ray', 'Blood test'
 --                       -- and link via FOREIGN KEY
 task_ts TIMESTAMP NOT NULL,  -- could have two blood tests twice on same day?
 --
 -- other fields
 --
 CONSTRAINT task_pk PRIMARY KEY (task_id)
);

• 互動任務。這個最終表是（技術術語）**關聯實體**的一個範例- 通常稱為連接表（或連結，或 m:n - 有很多名稱）。給定的互動可以與一個或多個任務相關聯，並且給定的任務可以與一個或多個互動相關聯。這是在 RDBMS 中對該場景進行建模的標準方法 - （有關更多詳細資訊，請參見此處）。

CREATE TABLE interaction_task -- could have series of tasks associated with 
(                              -- one interaction... - or just one...
 it_i_id INTEGER NOT NULL,
 it_t_id INTEGER NOT NULL,
 CONSTRAINT interaction_task_pk PRIMARY KEY (it_i_id, it_t_id),
 CONSTRAINT it_i_id_fk FOREIGN KEY (it_i_id) REFERENCES interaction (interaction_id),
 CONSTRAINT it_t_id_fk FOREIGN KEY (it_t_id) REFERENCES task (task_id)
);

如上所述，模式設計是迭代的，您最終的工作模式將取決於我不知道的許多因素。這個過程是這樣的

• 1 修改架構
• 2 調試/測試
• 3 回到第 1 步！

如果您有更具體的問題，請返回給我們 - 您的問題有點寬泛。

關於性能，在您的表格中有數百萬個條目之前，您不必開始擔心 - 任何類型的小型實踐（醫療、商業、法律……）都可以使用 HDD 在筆記型電腦上執行它 -不是我推薦這個 - 硬碟驅動器可能會失敗！

至少，擁有云備份策略和/或外部硬碟驅動器，並在安靜的時間（03:30？）執行備份 - 並保留書面記錄。

當/如果您變得忙碌時，您可能想投資一台小型伺服器 -使用 SSD 的RAID 1（鏡像）（價格按月下降） - 應該能夠滿足這個領域的大多數需求 - 儘管也許那樣時間，您將能夠負擔得起 sysadmin/dba 嗎？

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