Foreign Key column mapped to multiple primary keys

Question

I have a database that has three tables

Messages - PK = MessageId
Drafts - PK = DraftId
History - FK = RelatedItemId

The History table has one foreign key [RelatedItemId] , which maps to one of the two main keys in Messages and Drafts .

Is there a name for this relationship?

Is this just a bad design?

Is there a better way to develop this relationship?

Here are the CREATE TABLE suggestions for this question:

  CREATE TABLE [dbo].[History]( [HistoryId] [uniqueidentifier] NOT NULL, [RelatedItemId] [uniqueidentifier] NULL, CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED ( [HistoryId] ASC ) ) CREATE TABLE [dbo].[Messages]( [MessageId] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED ( [MessageId] ASC ) ) CREATE TABLE [dbo].[Drafts]( [DraftId] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_Drafts] PRIMARY KEY CLUSTERED ( [DraftId] ASC ) ) 

Answer 1

The best practice I found is to create a function that returns whether the passed value exists in any PK column of posts and drafts. Then you can add a constraint on the column in History that calls this function, and will only be inserted if it passes (i.e., it exists).

Adding an untreated example Code:

CREATE FUNCTION is_related_there (IN @value uniqueidentifier) ​​RETURN TINYINT START IF (select the counter (DraftId) from drafts, where DraftId = @value + select count (MessageId) from messages where MessageId = @value)> 0 THEN RETURN 1; ELSE RETURN 0; END IF; END

ALTER TABLE History ADD CONSTRAINT CK_HistoryExists CHECK (is_related_there (RelatedItemId) = 1)

Hope that works and helps lol

Answer 2

In a brief description, the solution you used is called:
Polymorphic Association
Purpose: link to multiple parents
Resulting anti-pattern: use a dual-use foreign key, violating the first normal form (atomic problem), losing referential integrity
Solution: Simplify Relationships

Details of the problem .

By the way, creating a common super-table will help you:

Answer 3

Is there a name for this relationship?

There is no standard name that I know of, but I heard people use the term “generic FK” or even “ internal platform effect” .

Is this just a bad design?

Yes.

Reason: it prevents you from declaring a FOREIGN KEY and therefore does not allow the DBMS to directly provide referential integrity. Therefore, you must enforce this imperative code, which is surprisingly difficult .

Is there a better way to develop this relationship?

Yes.

Create a separate FOREIGN KEY for each referenced table. Make them NULL-capable, but make sure one of them is not NULL by using the CHECK constraint.

Alternatively, look at inheritance .