Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Can you create one parent table, Alertable, with the following columns: ID, CreateDate, Name, Type. You can have your three child table FK to it, and your ALert will FK to only one table, Alertable. –  AlexKuznetsov Sep 25 '12 at 20:38
    
For some cases you make a good point -- effectively solution #3. Any time the data is fast moving or localized, however, it won't work. Say for example Product, Customer, and News each have a corresponding "Lang" table to support Name in several languages. If I have to deliver 'name' in the users native language I cannot store it in Alertable. That make any sense? –  EBarr Sep 25 '12 at 21:33
1  
@EBarr: " If I have to deliver 'name' in the users native language I cannot store it in Alertable. That make any sense?" No, it doesn't. With your current schema, if you have to deliver 'name' in the users native language, can you store it in Product,Customer or News table? –  ypercube Sep 26 '12 at 7:16
    
@ypercube I added that each of those tables has an associated language table. I was attempting to craft a scenario where "name" text can vary per request and thus cannot be stored in Alertable. –  EBarr Sep 26 '12 at 11:55
    
Unless it already has an accepted name, I propose the term "octopus join" for the query you'd do to see all alerts and their associated parents. :) –  Nathan Long Oct 26 '12 at 15:37

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
1  
Thanks for the input; I agree with most of your comments. I probably in-artfully described the required relationship (excluding solution 2 in your eyes), as it was a made up example. fn1 - understood, i was just simplifying tables a great deal to focus on the problem. fn2 - composite keys and I go way back! Re the lesser coupling schema I understand the simplicity, but personally try to design with DRI wherever possible. –  EBarr Sep 27 '12 at 14:39
    
Reviewing this, I began to doubt the correctness of my solution... anyway, it's been voted up twice, which I thank. Although I believe my design is valid but not suitable for the problem given, as the 'n' unions/joins are not addressed... –  mvaraujo Jan 13 '13 at 22:21
    
The DRI acronym got me. For you all, it stands for Declarative Referential Integrity, the technique behind referential data integrity which is commonly implemented as... (drum roll)... DDL FOREIGN KEY statements. More on en.wikipedia.org/wiki/Declarative_Referential_Integrity and msdn.microsoft.com/en-us/library/… –  mvaraujo Jan 13 '13 at 22:30

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.