My current design is makes me want to create a FK to a non-unique/non-PK. Is this a code smell and/or does my overall design make sense?
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 table contains a history of the connection requests and their status updates. I normalized the
target of the connection request into its own table so the target details wouldn't be duplicated with each status update. For example:
-- 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
As you can see connection status updates are handled by inserting a new row into the
ConnectionRequest table. The
UNIQUE constraint in the
ConnectionRequest table is there to ensure a
Target doesn't get multiple entries for the same status. Furthermore a new
ConnectionRequestTarget is generated for each request even if the user being targeted is the same. The
InternalUserId column in the
ConnectionRequestTarget table can be updated if the target wasn't a user, and then creates an account.
This worked great until a requirement came in to periodically prompt users with a push notification if they haven't acted(accept/deny) on a connection request. Due to that request I want to put a FK into the
PushSent table which will let me track the number of pushes sent to a target.
I can't set the FK to the
Target column in the
ConnectionRequest table because it is non-unique by design. I know I can put the
Status flag into the
PushSent table with the
Target, or handle this with a UDF and a check constraint, but those options feel a bit dirty. I could also specify a FK to the
ConnectionRequestTarget table directly from the
PushSent table but that could open the door for associating pushes to an orphaned target.
In truth it gets a bit more complicated than this because a
Target of a
ConnectionRequest can be a current member, or a potential member, but the core requirement to track the count of pushes/contacts to a