Schema for 1  0..1(+), many related but only one primary Using tools available in SQL Server 2008 and Entity Framework, what is a solid way to represent a one-to-many relationship, where there is an underlying unique relation that I need to enforce? My specific example is a set of people who have posted zero or more documents, but only one is their primary. It is not necessarily their latest.



Here's what I'm trying...

Can I enforce a relation both from DOCUMENT to PERSON on personId, and from PERSON to DOCUMENT on a composite key, while allowing null on primaryDocumentId? I'm a little worried this could cause me trouble in my ORM.

1. PERSON                       DOCUMENT
   personId unique        &lt;- -&gt; personId
   primaryDocumentId null    -&gt; documentId unique




Here are some other ideas:

2. PERSON                    DOCUMENT
   personId unique        &lt;- personId
   primaryDocumentId null    documentId unique


This does not prevent inconsistency of a person having someone else's document as their primary. It has the benefit that I can enforce a primary document by removing the null if I wish.

3. PERSON             DOCUMENT with trigger
   personId unique &lt;- documentId unique
                      personId
                      isPrimary


This has the problem of the searched/nested action the trigger could require under some circumstances. Also, I've entirely avoided triggers until now and would prefer to keep it that way, especially for core schematic needs.

4. PERSON             DOCUMENT
   personId unique &lt;- documentId unique
                      personId
                      date unique


This is fun, in that there is inherently only one oldest/newest document for each person. However, that doesn't represent my use in this scenario.

5. PERSON             DOCUMENT                          PRIMARYDOCUMENT
   personId unique &lt;- personId           / composite \  personId unique
                      documentId unique  \ relation  /  documentId


This looks like it might be the right way. I assume it is practiced, but I've never seen it before. Have you seen any risks associated with it?

Or any other suggestions?
Try a filtered unique index, not a trigger.

CREATE UNIQUE INDEX ixOnePrimaryEach ON dbo.Document(PersonID)
WHERE IsPrimary = 1;