Foreign Key column mapped to multiple primary keys

Question

I have a database which has three tables

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

The History table has a single foreign Key [RelatedItemId] which maps to one of the two Primary keys in Messages and Drafts.

Is there a name for this relationship?

Is it just bad design?

Is there a better way to design this relationship?

Here are the CREATE TABLE statements 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 )
)

would be better to provide `CREATE` statements. In some rdbms you can retrieve it like `SHOW CREATE TABLE Messages;` — Alexander, Mar 04 '14 at 16:42

Mohsen Heydari · Answer 1 · 2014-03-05T04:30:41.060

5

In a short description the solution you have used is called:
Polymorphic Association
Objective: Reference Multiple Parents
Resulting anti-pattern: Use dual-purpose foreign key, violating first normal form (atomic issue), loosing referential integrity
Solution: Simplify the Relationship

More information about the problem.

BTW createing a common super-table will help you:

enter image description here

edited Mar 05 '14 at 04:30

answered Mar 04 '14 at 18:07

Mohsen Heydari

7,256
4
31
46

score 3 · Answer 2 · edited May 23 '17 at 12:00

3

Is there a name for this relationship?

There is no standard name that I'm aware of, but I've heard people using the term "generic FKs" or even "inner-platform effect".

Is it just bad design?

Yes.

The reason: it prevents you from declaring a FOREIGN KEY, and therefore prevents the DBMS from enforcing referential integrity directly. Therefore you must enforce it trough imperative code, which is surprisingly difficult.

Is there a better way to design this relationship?

Yes.

Create separate FOREIGN KEY for each referenced table. Make them NULL-able, but make sure exactly one of them is non-NULL, through a CHECK constraint.

Alternatively, take a look at inheritance.

edited May 23 '17 at 12:00

Community

1
1

answered Mar 04 '14 at 17:41

Branko Dimitrijevic

50,809
10
93
167

Does the answer by Talspaugh27 allay your concerns about referential integrity? – bnieland Mar 04 '14 at 17:55
@bnieland Well, it allows two connections, where you specifically stated it "maps to one". But even assuming that's fixed, I don't see how it would avoid the race condition I mentioned in the link. And it's DBMS-specific (not just syntactically, some DBMSes don't support a CHECK that references other rows or tables at all). And it probably doesn't perform as well as "native" FK. And is less obvious and self-documenting. OK, I'll stop bashing it now ;) – Branko Dimitrijevic Mar 04 '14 at 18:12
@bnieland That being said, a careful implementation of a similar idea (that avoids the race condition) can save you a bunch of NULLs, but NULLs tend to be cheap anyway (especially under MS SQL Server, Oracle slightly less, YMMV). – Branko Dimitrijevic Mar 04 '14 at 18:15

Talspaugh27 · Accepted Answer · 2014-03-04T17:02:48.847

0

Best practice I have found is to create a Function that returns whether the passed in value exists in either of your Messages and Drafts PK columns. You can then add a constraint on the column on the History that calls this function and will only insert if it passes (i.e. it exists).

Adding non-parsed example Code:

CREATE FUNCTION is_related_there ( IN @value uniqueidentifier ) RETURNS TINYINT BEGIN IF (select count(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 runs and helps lol

edited Mar 04 '14 at 17:02

answered Mar 04 '14 at 16:51

Talspaugh27

973
9
16

A great answer, I am hoping to see what other people think! – bnieland Mar 04 '14 at 17:58
It is actually an approach that used for handling multi-level inheritance in a data-centric architecture. Have seen it used in both Java and .net applications – Talspaugh27 Mar 04 '14 at 18:09

Foreign Key column mapped to multiple primary keys

3 Answers3

Linked

Related