分解重複數據並用整數鍵替換它
我注意到在我的 SQLite 數據庫中，一些表包含許多行通常重複相同數據的列。
交易
例如，在這張交易表中，您可以看到發件人帳戶經常發送到同一個收件人帳戶，因此有很多重複的情況
( from_account_key, to_account_key )。您還可以看到這一點，( data_type )並且( data )在許多行中也具有相同的值。我的想法是將這些重複的非單整數列分解到自己的表中，然後通過整數鍵引用它們，這樣重複值就可以重複使用。（對此表執行此操作將生成表
Transaction ( key, transaction_accounts_key, timestamp, amount, data_type_key, data_key )、TransactionAccounts和）。DataType``Data這樣做的好處是重複數據較少，從而導致數據庫更小。缺點是 upserts 更加困難，因為需要更多的表和更多的邏輯，查詢變得更加複雜，並且最重要的是需要更多的連接來檢索原始列。
所以我的問題是，我應該走多遠？這裡有性能和空間之間的權衡嗎？
我所知道的是，
( data )blob 可能比較大，但會重複很多次，所以我肯定會將它們放在自己的表中，並通過整數鍵引用它們。我也會這樣做，( data_type )因為字元串可能會佔用相對較多的空間。但對我來說，這對於 2 個整數來說似乎有點矯枉過正( from_account_key, to_account_key )，那麼你在哪裡畫線呢？
您應該使用有關您正在使用的特定數據庫管理系統的更多資訊來更新您的問題，因為這會影響適用的答案。
例如，Microsoft SQL Server 提供開箱即用的 Page 和 Row 級別壓縮，因此您上面的冗餘值無論如何都會被壓縮，而您減少數據庫大小的努力將毫無意義。
Amazon Redshift 是一個基於列式 OLAP 的數據庫管理系統，因此您可能會發現在您的問題中包含範例數據的表的列的上下壓縮程度更高。（或者
clustered column store indexMicrosoft SQL Server 中的 a 也會產生類似的結果。）基本上，用整數外鍵替換冗餘值的主要好處可能是更好且更
Table normalization有效地更容易維護。僅當需要更改其中一個值並讓它更新表中的每個引用時，改進的可維護性才適用Transaction。例如，如果to_account_key事後需要將 40 的值更改為 90。（儘管基於您的列名中包含“鍵”一詞，但我認為這不是一個現實的案例。這種規範化有意義的一個範例是列舉表。）
關於鍵/表的任何決定都應牢記以下幾點：
- 鍵是確定實體之間唯一性和關係的數據元素
- 我們不能在沒有另一個鍵的情況下使用系統生成的值 (id) 來保持唯一性/引用完整性 - 否則它不會比 Excel 電子表格中的行號更好
- 作為 2. 的推論，任何使用系統生成的值作為鍵都需要另一個索引，該索引本身的成本可能大於將 10 字節降低到 4 字節所節省的任何儲存空間
- 如果數據庫正確規範化，則跨行重複的數據元素<>重複/重複的數據。如果我在交易表中的多行上有 AccountNumber，它不會重複，它是描述交易適用於哪個 Account 的元素。
- 數據庫中包含的表的數量主要由數據模型決定，任意的“更多表 = 壞”或“更少表 = 好”都沒有抓住重點我們的數據模型的實現應該主要關注維護數據模型的一致性。給定時間點的數據，而不是表將佔用多少空間
因此，關於您的範例，我將提出以下意見：
key只是一個行標識符，不提供直接訪問數據的方法，並且在這種情況下幾乎沒有用，因為實際的主鍵將是(from_account_key,timestamp)因為帳戶餘額的每次更改都應該是原子的1。- 如果存在現成的“帳號”或其他唯一且相對緊湊的現有數據元素，則定義帳戶的整數值可能是也可能不是最佳主意。否則，我們總是需要
Account在訪問Transaction.data_type對於看起來更接近交易類型的東西，這似乎不是正確的描述。這看起來也是一個用易於閱讀但更緊湊的縮短版本（通過參考表控制）替換值的好機會，例如“B”代表購買，“T”代表轉移，等等。- SQLite 不好的原因有很多（主要是讓您在任何列中插入任何內容），如果您實際上是在處理付款/處理資金（而不僅僅是儲存數據以進行分析/報告），那麼切換到更健壯的 RDBMS。
我已經看到很多數據庫試圖太花哨地試圖節省空間，最終結果通常是節省的空間被額外的索引用盡，這會佔用空間並導致更多的連接和讀取返回相同的數據量。忠實地為數據建模，然後尋找縮小寬鍵。
1如果您需要說服自己這一點，如果起始餘額為 50 美元，並且允許同時過帳兩筆 50 美元的交易，您將有負餘額。