ETL & SQL review

這個作業那時我花最多時間的是研究作業的data(restaurant_with_menu.json, users_with_purchase_history.json)去設計DB schema與ETL腳本。

ETL Review

ETL腳本
這邊最我認為花最多時間的是營業時間，資料裡餐廳有許多營業時間格式，花了很多時間去處理
資料裡的時間格式：

"Mon, Fri 2:30 pm - 8 pm / Tues 11 am - 2 pm / Weds 1:15 pm - 3:15 am / Thurs 10 am - 3:15 am / Sat 5 am - 11:30 am / Sun 10:45 am - 5 pm",
"openingHours": "Mon, Fri 3:15 pm - 7:30 pm / Tues - Weds 1 pm - 12 am / Thurs 1:15 pm - 4:45 pm / Sat 6:15 am - 3:15 am / Sun 5:30 pm - 10:30 pm",
"openingHours": "Mon-Thu, Sun 11 am - 10 pm  / Fri-Sat 11 am - 11 pm",
還有其他不同的時間格式例如Wed, Weds等等

優點

• 模組化：腳本分解成不同的功能模組，使每個部分的功能清晰，易於維護。例如，初始化DB schema的模組可以reuse。
• 日誌管理：使用日誌來追踪腳本的運行狀況可以幫助開發者快速定位和解決問題。例如，如果腳本在處理營業時間時出現問題，開發者可以查看日誌檔案來了解問題的具體原因。
• 錯誤處理：腳本的主要部分被包裹在一個 try...except 中，這有助於捕捉和記錄例外情況。例如，如果DB連接失敗，腳本會記錄錯誤並退出。
• 適配性：使用 config.json 檔案可以更改配置而不必修改腳本本身。例如，如果需要更改資料庫的連接資訊，只需修改 config.json 檔案即可。

缺點或可以改進的地方

• HardCode：部分data和檔案路徑（如日誌路徑或JSON檔案）HardCode，這可能會導致腳本在不同的環境中執行時出現問題。可以使用config或環境變數來設定這些值，以便在不同的環境中使用相同的腳本。
• 重複的Code：在處理營業時間時，有許多重複的code。這部分可以模組化或抽象化，以簡化腳本和減少可能的錯誤。例如，可以將處理時間的邏輯封裝到一個函數中，然後在需要的地方調用該函數。
• 缺乏注解：雖然Code的結構和功能模組相對乾淨，但在某些複雜的部分（特別是處理營業時間的部分）缺乏注解，我現在回頭看都要花很多時間去回憶。
• 資料庫操作：當執行 cur.execute 或 cur.executemany 之後，建議立即使用 conn.commit() 來確保資料庫事務被正確提交。
• 處理週末的映射：weekend 字典中有些天的名稱重複，例如 'Wed' 和 'Weds' 都映射到 '3'。這可能會導致某些日子被重複處理或遺漏。建議修改 weekend 字典，使每個天的名稱都唯一。
• 異常處理：雖然有一個 try...except 塊捕獲所有異常，但更具體的異常處理（例如數據庫連接失敗、數據格式錯誤等）可以提供更具體的錯誤信息。例如，可以使用特定的 except 子句來處理不同的異常，並在錯誤信息中包含更多詳細資訊。
• til_days 函數的命名：這個函數名稱可能不夠直觀，建議使用更具描述性的名稱，例如 "get_days_until"。

DB schema Review

Config.json
config中的create table sql：

Table "Restaurant" {
  "ID" SERIAL [pk, increment]
  "restaurantName" TEXT [not null]
  "cashBalance" NUMERIC [not null]
}

Table "Menu" {
  "ID" SERIAL [pk, increment]
  "dishName" TEXT [not null]
  "price" NUMERIC [not null]
  "restaurant_id" INT [not null]
}

Table "OpenHours" {
  "ID" SERIAL [pk, increment]
  "week" TEXT [not null]
  "open" time [not null]
  "close" time [not null]
  "restaurant_id" INT [not null]
}

Table "users" {
  "ID" INT [pk]
  "name" TEXT [not null]
  "cashBalance" NUMERIC [not null]
}

Table "PurchaseHistory" {
  "ID" SERIAL [pk, increment]
  "menu_id" INT [not null]
  "restaurant_id" INT [not null]
  "transactionAmount" NUMERIC [not null]
  "transactionDate" timestamp [not null]
  "user_id" INT [not null]
}

優點:

• 基礎清楚: Schema很基礎和清楚的定義了所需要的主要功能和關聯。
• 使用SERIAL: 使用SERIAL型別為主鍵確保了每次新增紀錄時ID都會自動增加。
• 非空約束: 使用[not null]確保了必須值。

缺點與改進之處:

• 缺少Foreign Key: restaurant_id、menu_id和user_id都是用來表示資料之間的關聯，但這些欄位並沒有foreign Key。例如，在Menu表中，應該將restaurant_id與Restaurant表的ID欄位建立foreign Key。
• Data integrity(數據完整性): cashBalance，可以增加CHECK約束確保其永遠是大於等於零。 在OpenHours表中，week欄位使用ENUM或CHECK約束來確保只能輸入有效的星期值，例如'Monday'到'Sunday'。
• 使用Index: 可以建立索引以提高查詢效能。例如，如果常按restaurantName進行查詢，則可為其建立索引。
• 正規化: 如果一個用戶可能在多家餐廳有購買記錄，則PurchaseHistory表中的restaurant_id和user_id組合可能會有重複。這可能意味著需要更進一步的正規化。
• 命名一致性: 資料表的名稱應該保持一致的單數或複數。例如users和Restaurant的table，應選擇一種命名方式並保持一致。

明天再來refactor這兩個部分。