1

I have a table called Message. Each message may be associated with an Invoice table or an Rfp table, but not both. I'm struggling with the best way to implement this:

  1. One approach is for the Message table to have foreign keys to both Invoice and Rfp tables. One FK would be valid while the other must be NULL. (Single-Table Inheritance.) But this seems rather awkward. In addition to the unused column, I need to find ways to prevent cases where the FKs are either both used or both NULL. And this would need to be repeated for every single message.

  2. Another approach is to create a joining table. In this case, my Message table would have an FK to the joining table, and the Invoice and Rfp tables would also have an FK to the joining table. However, the problem here is that, given a reference to the joining table, it is awkward to find the related Invoice or Rfp table because I wouldn't know which contains the FK. So, here, I need to resort to other steps to know how to find the related table, such as adding a column to signify which table is related, for which it is hard to create a constraint.

  3. Finally, I could create two types of Message tables. This solves the problems described above, but this causes problems for our app because we have logic in places that needs to add messages without knowing what type they are. (We can only have an FK to the related table.)

Can anyone offer some tips here. None of these ways are ideal, but perhaps there are aspects I've failed to consider when choosing between the two. Or perhaps there is a better approach altogether.

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466
  • I typically go with option no. 1 - yes, you end up having two FK columns, at least one of which is always null - but it's the cleanest and clearest approach in my opinion. It's clear from the FK columns what you're referring to, and you can have referential integrity on those FK constraints. – marc_s Feb 10 '12 at 05:55
  • @marc_s: But wouldn't option-2 be even more clear (normalized) approach? With the "joining" table be a supertype of the two subtype tables `Invoice` and `Rfp`? – ypercubeᵀᴹ Feb 10 '12 at 06:42
  • Option-2 would be the [Concrete Supertable](http://stackoverflow.com/questions/922184/why-can-you-not-have-a-foreign-key-in-a-polymorphic-association) example in Bill Karwin's answer in that question. – ypercubeᵀᴹ Feb 10 '12 at 06:52
  • An even more detailed explanation of how to properly implement your option-2 is (the Option-3 in @gbn's answer in another question): [NULLs in a composite primary key - SQL Server](http://dba.stackexchange.com/questions/9752/nulls-in-a-composite-primary-key-sql-server/9754#9754) – ypercubeᵀᴹ Feb 10 '12 at 06:54
  • @ypercube: Thanks for the links. In fact, option 2 was what I had settled on yesterday (and I was a little surprised when marc_s favored option 1). But I had second thoughts because, in our application, we would need some code to take action having only the ID of the joining table. If that code needs to reference the parent table, it's awkward because it doesn't know which parent table has the FK. I could add a column to indicate the related table, but this becomes something that's hard to create constraints for. – Jonathan Wood Feb 10 '12 at 14:45
  • @JonathanWood: You want to reference the parent table (I guess you mean the `Invoice` or the `Rfp` depending on the Id. In that case, you'll want data from similar columns of these 2 bles, right? So, a join like: `FROM Message m LEFT JOIN Invoice i ON m.id=i.id LEFT JOIN Rfp r ON m.id=r.id` would work with some use of `COALESCE()` function in the `SELECT` list. – ypercubeᵀᴹ Feb 10 '12 at 20:02
  • @ypercube: Yes, thanks for that, although we are working with Entity Framework. At any rate, I am moving forward with my option 2 but am adding a "type" column and currently looking into the ways to have that fully constrained (as described in [this article](http://sqlblog.com/blogs/alexander_kuznetsov/archive/2011/10/17/trusted-foreign-keys-allow-orphans-reject-valid-child-rows.aspx)). – Jonathan Wood Feb 10 '12 at 20:07
  • It seems similar to gbn's asnwer in my second link. `Pet, Cat, Dog` vs `Vehicle, Car, Truck`. No idea about any EF particular problems that may arise with such a solution. – ypercubeᵀᴹ Feb 10 '12 at 20:19
  • @ypercube: Yes, pretty much the same approach. I do not believe there is any way to specify a LEFT OUTER JOIN in the current version of Entity Framework. – Jonathan Wood Feb 10 '12 at 20:23
  • Can you specify a `UNION` ? Because usually outer joins and unions are connected. You can do something like this, for example: `(SELECT ... FROM Message m JOIN Invoice i ) UNION (SELECT ... FROM Message m JOIN Rfp r ...)` – ypercubeᵀᴹ Feb 10 '12 at 20:24

1 Answers1

2

In option 1, you can use a check constraint to ensure only one FK is set...

CREATE TABLE [dbo].[Rfp] (Id int IDENTITY(1,1) NOT NULL, PRIMARY KEY CLUSTERED (Id))
CREATE TABLE [dbo].[Invoice] (Id int IDENTITY(1,1) NOT NULL, PRIMARY KEY CLUSTERED (Id))

CREATE TABLE dbo.[Message] (Id int IDENTITY(1,1) NOT NULL, RfpId int, InvoiceId  int, 
   PRIMARY KEY CLUSTERED (Id),
   FOREIGN KEY (RfpId) REFERENCES [dbo].[Rfp] (Id),
   FOREIGN KEY (InvoiceId) REFERENCES [dbo].[Invoice] (Id),
   )

ALTER TABLE dbo.[Message] 
   ADD CONSTRAINT CK_FK CHECK (   (RfpId IS NULL AND InvoiceId IS NOT NULL) 
                                     OR (RfpId IS NOT NULL AND InvoiceId IS NULL));
Phil Bolduc
  • 1,586
  • 1
  • 11
  • 19