如何設計避免重複的外鍵

我不確定如何命名這個問題。我有三個表使用者，照片和相冊。一個使用者有很多照片對象和很多相冊對象。但是，在使用者添加給定照片後，他們可以選擇將其放入單個相冊中（照片不必與相冊連結）。

當照片與相冊關聯以避免數據庫異常時，如何繞過冗餘使用者列（Album.user 和 Photo.user）？

我無法擺脫照片上的使用者列，因為如果它未連結到 / 已從相冊中刪除，我將無法訪問它。專輯也是如此。我有一些規範化表的經驗，但我不知道如何在不使用 db 約束的情況下避免這種情況。

這是一個粗略的模式圖像：

使兩者都可以為空user_id，但添加一個檢查約束album_id以photo確保它們中的一個被設置。如果您不能在同一語句中更改這兩個屬性，則可以使用延遲約束觸發器而不是檢查約束。

一種反思是，擁有相冊和照片的所有者可能是有意義的。可以想像應該允許一個使用者將其他使用者的照片添加到他們的相冊中。

除此之外，一個想法是添加一個超類型 - 比如說 photo_collection - 它可以是專輯或單張照片，並讓 user_id 成為一個屬性。

另一個變體是在 ALBUM 中添加唯一約束：

ALTER TABLE album ADD CONSTRAINT ak_album
   UNIQUE (user_id, id);

現在您可以在 PHOTO 中將其引用為（替換目前的 fk）：

ALTER TABLE photo ADD CONSTRAINT fk_album
   FOREIGN KEY (user_id, album_id)
   REFERENCES album (user_id, id)

這將保證相關照片和相冊具有相同的 user_id

第三種選擇是始終為每張照片設置一個預設相冊，當最後一張照片從預設相冊中移出時，可以刪除該相冊。IE

USER <- ALBUM <- PHOTO

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