Design Pattern - One of Many Parent Tables

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

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.

StackExchange Database Administrators Q#24903, answer score: 4