如何在應用約束時查詢地址“預測”
假設我有這個簡化的數據庫方案。
該方案由三個表組成，街道、城市和郵政編碼，它們之間都具有多對多的關係。
我想要做的是查詢數據庫以查找可能的匹配項以及預測缺失數據的預測。舉個例子：假設使用者開始輸入一個城市，也許是“Wash”。他已經輸入了一條街道，為了我不知道任何街道，我們稱它為“新街大道”。這是一個約束，意味著它必須在我們的預測中實現。現在我希望城市查詢“Wash”的可能完成者，並提供缺失的數據，在這種情況下，郵政編碼有一條名為“New Street Avenue”的街道
以下是天真的方法：
- 過濾名稱與“New Street Avenue”匹配的所有鍵的街道
- 過濾所有街道鍵的 street_city 並返回城市鍵
- 內連接 city_zipcode 上的城市鍵，然後內連接這些到郵政編碼
**這種方法的問題：**街道和郵政編碼之間的知識被完全忽略了。這意味著我們最終可能會得到郵政編碼和城市對，我們知道城市包含我們尋找的街道，但郵政編碼不一定。這將是一個無效的返回預測，因為地址不存在！
這意味著我需要“持久化”有關街道的資訊。
我的方法是這樣的：
- 在街道上過濾滿足我們條件的鍵。
- 從#1 到城市的內部連接街道的鑰匙。保留兩個關鍵列。
- 內部連接到 city_zipcode 並保留郵政編碼鍵
- 過濾掉所有不在 street_zipcode 中的街道和郵政編碼對
（附帶說明：為了“希望”提高性能，在每個“預測”之後，我會放一個 LIMIT 10（或類似的）命令，因為我們只需要合理數量的預測）。
這種方法應該有效。它可能不是最好的優化，但它會返回正確的結果。然而，這不僅讓我覺得很髒，而且還帶來了另一個問題：
我很確定它在表格大小以及其他表格中的擴展非常可怕
很有可能會有另一個表與街道和郵政編碼具有多對多關係。現在，如果我想搜尋一個有街道約束的城市，我需要做我上面所做的，並添加更多內容以過濾掉街道和新表之間的任何關係。
我覺得我用這種方法進入了死胡同。我很想有人在這裡幫助我如何更好地解決這個問題。
3 個表就是我所說的“過度規範化”——它會導致性能問題。
“規範化”用於兩個目的，這兩個目的都不適用於您的案例。
- 隔離一些東西，說“城市”，這樣就可以很容易地改變它。在現實生活中，這種情況很少發生在城市中。（“孟買”->“孟買”（印度），“溫泉”->“真相或後果”（新墨西哥州））
- 節省空間。即使你擁有地球上所有的 300 萬個城市，每次將它們拼出國家也不會有太大的空間負擔。（對於國家/地區，我推薦標準的 2 字母國家/地區程式碼並使用
CHARACTER SET ascii。可選地，有一個表格將它們映射到拼寫名稱。）郵政編碼：在美國，大約有 42K 郵政編碼。要規範化，您可以使用 2-byte
SMALLINT UNSIGNED。但是郵政編碼本身可以儲存在 3-byteMEDIUMINT(5) UNSIGNED ZEROFILL中。郵政編碼確實發生了變化。但這通常涉及迫使一個郵政編碼的一半使用者採用“新”郵政編碼。您必須檢查該郵政編碼的所有使用者（可能是INDEXed）才能確定要更改的使用者。南斯拉夫和捷克斯洛伐克的分裂也是如此。另一方面，將“上沃爾特共和國”更改為“布基納法索”很容易，如果您已將其“標準化”。
計劃 A（數百萬個位置）：簡單地拼出每個項目的位置。
B計劃（十億個位置）：一分為二：街道地址和郵編+城市+國家
換一種方式想一想：您將如何處理該地址？
- 只用於發送郵件？那為什麼要分開；只需有一些可以按原樣輕鬆列印的文本。
- 也用於統計？然後你需要，比如說，“國家”（或“國家程式碼”），這樣你就可以做到
SELECT SUM... GROUP BY country_code。這意味著這country_code是一個單獨的列。