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.

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        <- -> personId
   primaryDocumentId null    -> documentId unique

Here are some other ideas:

2. PERSON                    DOCUMENT
   personId unique        <- 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 <- 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 <- 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 <- 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?

share|improve this question
1  
See my answer here for a similar problem (in MySQL): In SQL, is it OK for two tables to refer to each other? Approach #2 is exactly what @Rob proposed and works fine in SQL-Server. Approach #4 works in most DBMS but requires an additional table (PRIMARY_DOCUMENT), exactly as your last proposal. –  ypercube Aug 25 '12 at 9:04
    
I don't know your application, but chances are, you would be interested in more than the document id for a person. You probably want to collect, type, dates, etc. which would call for a separate document table. –  Emmad Kareem Aug 25 '12 at 9:30
    
@Emmad: He already has a DOCUMENT table. –  ypercube Aug 25 '12 at 9:38
    
ypercube: you are correct, it is the same question at the root. great reference. For education, would you please explain why you say a circular reference (first option on both my and your post) is bad here. The bilateral relation on the unique personId (although via two relations, still concurrent) here ensures there is no recursion. Unless, of course, you could consider a 1-1 relationship recursive. Are you saying it's just a matter of how poorly supported this is across DBMS or ORMs? –  shannon Aug 25 '12 at 20:52
    
No, I don't mind 1-1 relationships. I don't like circular references in FKs in general, not just in this case. There are problems with inserting and deleting rows from both the 2 tables (or all if there more than 2 tables involved, or less than 2). FKs with cascading delete in not well supported, in some DBMS when there are circular references. I find it cleaner (and possible to do in every case) to remove them by adding one or two tables which represent entities that are not obvious. In this case the "Primary Document for a person". –  ypercube Aug 26 '12 at 13:36

1 Answer 1

up vote 3 down vote accepted

Try a filtered unique index, not a trigger.

CREATE UNIQUE INDEX ixOnePrimaryEach ON dbo.Document(PersonID)
WHERE IsPrimary = 1;
share|improve this answer
    
awesome, thanks. just the sort of solution I was looking for. –  shannon Aug 25 '12 at 17:53
    
it's probably also worth noting that, as ypercube mentioned in the related question he linked above, this does rely on features in my specific DBMS –  shannon Aug 25 '12 at 20:58

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.