Design Pattern - One of Many Parent Tables

Question

I come across a situation in database quite frequently where a given table can FK to one of a number of different parent tables. I've seen two solutions for the problem, but neither is personally satisfying. I'm curious what other patterns you've seen out there? Is there a better way to do it?

A Contrived Example
Let's say my system has Alerts. Alerts can be received for a variety of objects -- Customers, News, and Products. A given alert can be for one-and-only one item. For whatever reason Customers, Articles and Products are fast moving (or localized) so the necessary text/data cannot be pulled into Alerts upon creation of an Alert. Given this setup I've seen two solutions.

Note: Below DDL is for SQL Server but my question should be applicable to any DBMS.

Solution 1 -- Multiple Nullable FKeys

In this solution the table that links to one-of-many tables has multiple FK Columns (for brevity's sake the below DDL doesn't show FK creation). THE GOOD - In this solution it's nice that I have foreign keys. The null-optinality of the FK's makes this convenient and relatively easy to add accurate data. THE BAD Querying isn't great because it requires N LEFT JOINS or N UNION statements to get the associated data. In SQL Server, specifically the LEFT JOINS preclude creating an indexed view.

CREATE TABLE Product (
    ProductID    int identity(1,1) not null,
    CreateUTC    datetime2(7) not null,
     Name        varchar(100) not null
    CONSTRAINT   PK_Product Primary Key CLUSTERED (ProductID)
)
CREATE TABLE Customer (
    CustomerID  int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
     Name       varchar(100) not null
    CONSTRAINT  PK_Customer Primary Key CLUSTERED (CustomerID)
)
CREATE TABLE News (
    NewsID      int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    Name        varchar(100) not null
    CONSTRAINT  PK_News Primary Key CLUSTERED (NewsID)
)

CREATE TABLE Alert (
    AlertID     int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    ProductID   int null,
    NewsID      int null,
    CustomerID  int null,
    CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

ALTER TABLE Alert WITH CHECK ADD CONSTRAINT CK_OnlyOneFKAllowed 
CHECK ( 
    (ProductID is not null AND NewsID is     null and CustomerID is     null) OR 
    (ProductID is     null AND NewsID is not null and CustomerID is     null) OR 
    (ProductID is     null AND NewsID is     null and CustomerID is not null) 
)

Solution 2 -- One FK in each Parent Table
In this solution each 'parent' table has an FK to the Alert table. It makes it easy to retrieve alerts associated with a parent. On the down side, there is no real chain from the Alert to who references. Further, the data model allows for orphaned Alert's -- where an alert is not associated with a Product, News, or Customer. Again, multiple LEFT JOINs to figure out the association.

CREATE TABLE Product (
    ProductID    int identity(1,1) not null,
    CreateUTC    datetime2(7) not null,
     Name        varchar(100) not null
    AlertID     int null,
    CONSTRAINT   PK_Product Primary Key CLUSTERED (ProductID)
)
CREATE TABLE Customer (
    CustomerID  int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
     Name       varchar(100) not null
    AlertID     int null,
    CONSTRAINT  PK_Customer Primary Key CLUSTERED (CustomerID)
)
CREATE TABLE News (
    NewsID      int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    Name        varchar(100) not null
    AlertID     int null,
    CONSTRAINT  PK_News Primary Key CLUSTERED (NewsID)
)

CREATE TABLE Alert (
    AlertID     int identity(1,1) not null,
    CreateUTC   datetime2(7) not null,
    CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

Is this just life in a relation database? Are there alternate solutions you've found more satisfying?

Answer 1

I understand the second solution as not applicable as it does not offer one (object)-to-many (alert) relationship.

You are stuck to just two solutions because of the strict 3NF compliance.

I would design a lesser coupling schema:

CREATE TABLE Product  (ProductID  int identity(1,1) not null, ...)
CREATE TABLE Customer (CustomerID int identity(1,1) not null, ...)
CREATE TABLE News     (NewsID     int identity(1,1) not null, ...)

CREATE TABLE Alert (
  -- See (1)
  -- AlertID     int identity(1,1) not null,

  AlertClass char(1) not null, -- 'P' - Product, 'C' - Customer, 'N' - News
  ForeignKey int not null,
  CreateUTC  datetime2(7) not null,

  -- See (2)
  CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertClass, ForeignKey)
)

-- (1) you don't need to specify an ID 'just because'. If it's meaningless, just don't.
-- (2) I do believe in composite keys

Or, if integrity relationship shall be mandatory, I might design:

CREATE TABLE Product  (ProductID  int identity(1,1) not null, ...)
CREATE TABLE Customer (CustomerID int identity(1,1) not null, ...)
CREATE TABLE News     (NewsID     int identity(1,1) not null, ...)

CREATE TABLE Alert (
  AlertID     int identity(1,1) not null,
  AlertClass char(1) not null, /* 'P' - Product, 'C' - Customer, 'N' - News */
  CreateUTC  datetime2(7) not null,
  CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

CREATE TABLE AlertProduct  (AlertID..., ProductID...,  CONSTRAINT FK_AlertProduct_X_Product(ProductID)    REFERENCES Product)
CREATE TABLE AlertCustomer (AlertID..., CustomerID..., CONSTRAINT FK_AlertCustomer_X_Customer(CustomerID) REFERENCES Customer)
CREATE TABLE AlertNews     (AlertID..., NewsID...,     CONSTRAINT FK_AlertNews_X_News(NewsID)             REFERENCES News)

Anyway...

Three valid solutions plus another to be considered for many (objects)-to-one (alert) relationships...

These presented, what's the moral?

They differ subtly, and weight the same on the criterias:

• performance on insertions and updatings
• complexity on querying
• storage space

So, choose that comfier to you.

Answer 2

I have used trigger-maintained join tables. the solution works pretty well as a last resort if refactoring the db isn't possible or desirable. The idea is that you have a table which is there just to handle the RI issues, and all DRI goes against it.