鼓勵 FK 到非唯一/非 PK 的設計
我目前的設計是讓我想創建一個非唯一/非 PK 的 FK。這是程式碼異味和/或我的整體設計是否有意義？
CREATE TABLE [User].[ConnectionRequest] ( [ConnectionRequestId] INT NOT NULL PRIMARY KEY IDENTITY, [Requestor] INT NOT NULL, -- FK to internal user table [Target] INT NOT NULL, -- FK to ConnectionRequestTarget table [Status] INT NOT NULL, -- FK to status enum descriptions [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(), CONSTRAINT [FK_Name_Truncated_For_Space] FOREIGN KEY ([Target]) REFERENCES [User].[ConnectionRequestTarget]([ConnectionRequestTargetId]), CONSTRAINT [Unique_ConnectionRequest_Target_Status] UNIQUE ([Target], [Status]) -- Other FK's removed for brevity ) CREATE TABLE [User].[ConnectionRequestTarget] ( [ConnectionRequestTargetId] INT NOT NULL PRIMARY KEY IDENTITY, [InternalUserId] INT NULL, -- FK to internal user table, can be updated [ReferralId] INT NULL, -- FK to referral table [RequestCreated] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(), -- FK's removed for brevity ); CREATE TABLE [User].[PushSent] ( [PushSentId] INT NOT NULL PRIMARY KEY IDENTITY, [MatchingToken] UNIQUEIDENTIFIER NOT NULL, [PushDeviceId] INT NOT NULL, [Category] INT NOT NULL, [MonitoringId] INT NULL, [ConnectionRequestTargetId] INT NULL, [ThresholdForNextAlert] DATETIME2(2) NULL, [Scheduled] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(), [Sent] DATETIME2(2) NULL, CONSTRAINT [FK_PushSent_ConnectionRequestTargetId_to_ConnectionRequest_Target] FOREIGN KEY ([ConnectionRequestTargetId]) REFERENCES [User].[ConnectionRequest]([Target]), -- This doesn't work -- Other FK's and check constraints removed for brevity );該
ConnectionRequest表包含連接請求及其狀態更新的歷史記錄。我將連接請求規範化target到它自己的表中，這樣每次狀態更新都不會重複目標詳細資訊。例如：-- ConnectionRequest table sample data ConnectionRequestId | Requestor | Target | Status | Created 1 1 1 1 2015-10-3 10:31:23.93 -- Request Pending 2 2 2 1 2015-10-3 10:31:25.89 -- Request Pending 3 1 3 1 2015-10-3 11:45:12.49 -- Request Pending 4 1 1 3 2015-10-3 12:45:12.49 -- Request Accepted 5 1 4 1 2015-10-3 12:45:12.49 -- Request Pending -- ConnectionRequestTarget table sample data ConnectionRequestTargetId | InternalUserId | ReferralId | RequestCreated 1 3 NULL 2015-10-3 10:31:23.93 -- Internal 2 3 NULL 2015-10-3 10:31:25.89 -- Internal 3 NULL 1 2015-10-3 11:45:12.49 -- External 4 4 2 2015-10-3 12:45:12.49 -- Updated
ConnectionRequest如您所見，連接狀態更新是通過在表中插入新行來處理的。表中的(Target, Status)UNIQUE約束ConnectionRequest是為了確保 aTarget不會獲得相同狀態的多個條目。此外ConnectionRequestTarget，即使目標使用者相同，也會為每個請求生成一個新的。如果目標不是使用者，則可以更新表中的InternalUserId列，然後創建一個帳戶。ConnectionRequestTarget這很有效，直到需要定期提示使用者如果他們沒有對連接請求採取行動（接受/拒絕）時發出推送通知。由於該請求，我想將 FK 放入
PushSent表中，這將讓我跟踪發送到目標的推送次數。我無法將 FK 設置為表中的
Target列，ConnectionRequest因為它在設計上是非唯一的。我知道我可以使用 將Status標誌放入PushSent表中Target，或者使用UDF 和檢查約束來處理它，但是這些選項感覺有點臟。我也可以ConnectionRequestTarget直接從PushSent表中為表指定一個 FK，但這可能會打開將推送關聯到孤立目標的大門。事實上，它變得比這更複雜一些，因為 a
Target可以ConnectionRequest是目前成員，也可以是潛在成員，但跟踪對 a 的推送/聯繫計數的核心要求Target仍然存在。
在我看來，您有一個實體Target，您可能有一個活動的ConnectionRequest。隨著請求的處理，請求的狀態似乎會不時更改。在這種情況下，該欄位不應成為任何鍵或唯一約束的一部分。只有穩定（不變）的欄位才能成為鍵的一部分。在這種情況下，Created欄位似乎是完美的。
但是，我根本不明白為什麼需要創建唯一約束。您已經為每個請求分配了一個唯一的 PK，為什麼沒有ConnectionRequestTargetId參考ConnectionRequest( ConnectionRequestID )？這將安全地將每個PushSent 綁定到一個特定請求。
根據ypercube 的評論：
為什麼
Target需要在PushSent表中？你不能只使用一個ConnectionRequestId列（這將是一個 FK 到ConnectionRequest）？我意識到我錯誤地處理了這個問題。
Target在我發展出隧道視野之前，我一直專注於這種關係。最後，我決定不對
ConnectionRequest牌桌或ConnectionRequestTarget我牌桌上的牌桌持有 FKPushSent。我最終創建了下表來記錄每次聯繫嘗試：CREATE TABLE [Log].[ContactAttempt] ( [ContactAttemptId] INT NOT NULL PRIMARY KEY, [Method] INT NOT NULL, -- FK to enum description table, 1 = SMS, 2 = Email, 3 = Push [ConnectionRequestId] INT NOT NULL, -- FK to ConnectionRequest table [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME() -- FK definitions removed for brevity )使用該表，我可以使用以下查詢獲得所需的資訊：
DECLARE @Now DATETIME2(2) = CAST(SYSUTCDATETIME() AS DATETIME2(2)); ;WITH [NotificationCount_CTE] AS ( SELECT [CR].[ConnectionRequestId] , DATEADD(DAY, 1, [CR].[Created]) AS [FirstCutoff] , DATEADD(DAY, 4, [CR].[Created]) AS [SecondCutoff] , DATEADD(DAY, 7, [CR].[Created]) AS [ThirdCutoff] , CASE -- This could probably be improved WHEN MAX([CA].[Created]) > DATEADD(DAY, 7, [CR].[Created]) THEN 4 ELSE CASE WHEN MAX([CA].[Created]) > DATEADD(DAY, 4, [CR].[Created]) THEN 3 ELSE CASE WHEN MAX([CA].[Created]) > DATEADD(DAY, 1, [CR].[Created]) THEN 2 ELSE 1 END END END AS [ContactCount] -- Use a CASE statements so early counts aren't -- included in the filter below FROM [User].[ConnectionRequest] [CR] LEFT JOIN [Log].[ContactAttempt] [CA] ON [CA].[ConnectionRequestId] = [CR].[ConnectionRequestId] WHERE [CR].[Status] = 1 -- Pending status. GROUP BY [CR].[ConnectionRequestId] , [CR].[Created] ) SELECT [PDA].[DeviceToken] , ISNULL([PDA].[PushDeviceId], 0) AS [PushDevice] , [PDA].[PushSystem] , CASE WHEN [CCR].[InternalUserId] IS NULL THEN [CCR].[ContactInfo] ELSE ISNULL([SU].[Email], [U_T].[PhoneNumber]) END AS [ContactInfo] -- External users invititation contact. -- Internal users use email first, phone number second. , [U_R].[Fullname] -- Name of person who initiated contact request , [U_R].[DisplayName] -- Fall back if they didn't provide a full name , [PDA].[CurrentPushCount] , [CR].[ConnectionRequestId] AS [RequestId] , [CCR].[ReferralCode] -- Code for tracking external invites via a link FROM [User].[ConnectionRequest] [CR] INNER JOIN [User].[CurrentConnectionRequestStatus] [CCR] ON [CCR].[Target] = [CR].[Target] INNER JOIN [NotificationCount_CTE] [NC] ON [NC].[ConnectionRequestId] = [CR].[ConnectionRequestId] LEFT JOIN [User].[CurrentPushDeviceAssociation] [PDA] WITH (NOEXPAND) ON [PDA].[UserId] = [CCR].[InternalUserId] LEFT JOIN [User].[User] [U_R] ON [U_R].[UserId] = [CCR].[Requestor] LEFT JOIN [User].[User] [U_T] ON [U_T].[UserId] = [CCR].[InternalUserId] LEFT JOIN [Security].[User] [SU] ON [SU].[UserId] = [CCR].[InternalUserId] WHERE [CCR].[Status] = 1 -- We only remind if the request is still pending AND ( (@Now > [NC].[FirstCutoff] AND [NC].[ContactCount] = 1) OR (@Now > [NC].[SecondCutoff] AND [NC].[ContactCount] = 2) OR (@Now > [NC].[ThirdCutoff] AND [NC].[ContactCount] = 3) );引用是一個視圖，它顯示了連接的
CurrentConnectionRequestStatus目前狀態，並對一些關聯數據進行了非規範化。除了ypercube 提供的關鍵見解之外，我真的不想要/不需要真正的推送/聯繫計數。我真正想要的是看看是否在特定的時間間隔內進行了任何联系嘗試。通過將各種聯繫方式組合到一張表中，我可以輕鬆跟踪嘗試過的聯繫方式以及嘗試的時間。
明確回答我原來的問題：
- 這是程式碼味道嗎？
- 是的，這是我的狹隘視野造成的程式碼氣味。一旦我睜開眼睛，我就能消除明顯的衝突。
- 我的整體設計有意義嗎？
- 需要調整整體設計以清晰地反映需求。