對我的數據庫設計以及如何處理空值/測量錯誤的建議

我正在為我自己的數據庫設計尋求建議。我需要邁出一大步，開始大量的數據處理。我現在的決定將對我的項目產生重大影響。

我有這張桌子。它保存從外部來源線上獲取的數據。

app_data
+--------------+-------+------------+--------+---------+-------+
| surrogateKey | appid |  created   | owners | viewers | price |
+--------------+-------+------------+--------+---------+-------+
|            1 |     2 | 1472428100 | 10     |      25 | 10,00 |
|            2 |     2 | 1472428200 | 11     |      50 | 10,00 |
|            3 |     2 | 1472428300 | 15     |      50 | 10,00 |
|            4 |     2 | 1472428400 | 22     |      51 |  8,00 |
|            5 |     2 | 1472428500 | null   |      50 |  8,00 |
|            6 |     2 | 1472428600 | 20     |      49 |  8,00 |
|            7 |     2 | 1472428700 | 25     |      50 | 10,00 |
|          ... |       |            |        |         |       |
+--------------+-------+------------+--------+---------+-------+

CREATE TABLE app_data(
   surrogateKey BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
   appID INT UNSIGNED NOT NULL,
   created TIMESTAMP NOT NULL,
   owners INT UNSIGNED,
   owners_variance MEDIUMINT UNSIGNED,
   viewers MEDIUMINT UNSIGNED, -- TINYINT would be better but application layer cannot handle tinyint
   viewers_variance MEDIUMINT UNSIGNED,
   price MEDIUMINT UNSIGNED,
   CONSTRAINT fk_appData_app_appID 
       FOREIGN KEY (appID) 
       REFERENCES app(appID)
       ON UPDATE CASCADE
) ENGINE=INNODB;

1. 創建這個 surrogateKey 有意義嗎？它沒有增加任何價值。我不會用於連接。appid 和 created 將成為一個很好的複合鍵，我將使用這些鍵來連接表。
2. appID 和 created 將用於連接、比較、範圍查詢等。我應該如何創建索引？每個索引一個？（appID，創建）？（創建，appID）？
3. 這個表將是巨大的並佔用 95% 的儲存空間。我怎樣才能進一步優化它的性能？
4. 我應該如何處理所有者列中的隨機空值？這些基本上意味著：那天溫度計壞了……我正在考慮將它們調為零或將它們保留為空。null 確實意味著當天沒有測量值，而零值會模棱兩可但更易於分析。
5. 我應該如何處理沒有意義的值？例如觀眾上限為 50。有一個值為 51 的值表示：此值是錯誤的。我應該把它變成空嗎？到50？
6. 我應該將價格標準化為這樣的東西嗎？規範化會使分析變得更加困難，並導致更複雜的查詢。

.

CREATE TABLE(
 appID INT NOT NULL PRIMARY KEY,
 created TIMESTAMP,
 start_date TIMESTAMP,
 end_date TIMESTAMP,
 price INT
);

謝謝！

其中一些問題與其說是關於數據庫設計，不如說是關於數據清理和分析——在這些情況下，正確答案實際上取決於您的分析任務的具體情況。

1. 如果代理鍵沒有增加價值（您甚至不會將它用於連接）並且您確定它(appid, created)始終是唯一的，那麼只需將(appid, created)其用作複合主鍵。
2. 如果您總是一起加入、查詢等(appid, created)，那麼將這些列分配為多列主鍵所產生的索引將為您提供所需的內容。除非您還要獨立查詢兩列之一，否則順序應該無關緊要。（編輯：Rick James 提出了一個很好的觀點，即在 . 上查詢相等性appid和某個BETWEEN範圍的可能性created。正如他所說，在這種情況下，請(appid, created)選擇您的訂單。）
3. 這取決於您將要進行的查詢。你能提供例子嗎？在需要它的地方建立索引並避免連接是您最簡單的選擇。
4. 這確實是一個數據分析問題——但總的來說，我強烈建議將缺失值保持為空，並根據給定操作的需要轉換或刪除這些值。根據您為缺失執行適當的無操作值的分析，可能會有所不同（例如0，對於總和/平均值，但1對於產品等），或者您可能只是希望能夠完全排除這些行，但仍然有記錄他們被記錄下來。
5. 同樣，這在很大程度上是一個數據清理/分析決策 - 但總的來說，我會保持觀察到的數據完好無損（即使您認為它是無效的/非物理的/否則不可能），並且僅在分析期間將其丟棄。假設你有儲存空間，更多的資訊總是比更少的好——如果你後來發現一些所謂的錯誤值實際上是可能的怎麼辦？
6. 從性能的角度來看，我會避免需要連接表，但從一致性/參照完整性的角度來看，這顯然是可取的。

如果price定期更改，則可以將您的created列分為兩列：一列描述價格不變的較粗略的時間段，另一列描述該更廣泛時期內更精細的時間戳。例如：

app_data:

|-------+------------------+----------------+--------+---------+-------|
| appid | coarsetimeperiod | finetimeperiod | owners | viewers | price |
|-------+------------------+----------------+--------+---------+-------|
|     2 |                1 |              1 |     10 |      25 | 10.00 |
|     2 |                1 |              2 |     11 |      50 | 10.00 |
|     2 |                1 |              3 |     15 |      50 | 10.00 |
|     2 |                2 |              1 |     22 |      51 |  8.00 |
|     2 |                2 |              2 |   null |      50 |  8.00 |
|     2 |                2 |              3 |     20 |      49 |  8.00 |
|     2 |                3 |              1 |     25 |      50 | 10.00 |
|-------+------------------+----------------+--------+---------+-------|

您將有一個FOREIGN KEY (coarsetimeperiod, price) REFERENCE price_data(coarsetimeperiod, price)定義 on app_data，price_data表格如下所示：

price_data :
|------------------+-------|
| coarsetimeperiod | price |
|------------------+-------|
|                1 | 10.00 |
|                2 |  8.00 |
|                3 | 10.00 |
|------------------+-------|

這將為您帶來規範化的參照完整性優勢，但不會因需要連續執行連接而造成性能損失。

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