Sql-server – Schema for 1 <-> 0..1(+), many related but only one primary

database-designentity-frameworkinformation-schemaschemasql server

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?

Best Answer

Try a filtered unique index, not a trigger.

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

Related Solutions

Why Apply Constraints in Database Rather Than Code?

Some constraints are best enforced in the database, and some are best enforced in the application.

Constraints that are best enforced in the database are usually there because they are fundamental to the structure of the data model, such as a foreign key contraint to ensure that a product has a valid category_id.

Constraints that are enforced in an application might not be fundamental to the data model, such as all FooBar products must be blue - but later someone might decide that FooBars can also be yellow. This is application logic that doesn't really need to be in the database, though you could create a separate colours table and the database can require that the product reference a valid entry from that table. BUT the decision that the only record in colours has the value blue would still come from somewhere outside the database.

Consider what would happen if you had no constraints in the database, and required them to all be enforced in the application. What would happen if you had more than one application that needed to work with the data? What would your data look like if the different applications decide to enforce contraints differently?

Your example shows a situation where it might have been more beneficial to have the constraint in the application rather than in the database, but perhaps there was a fundamental problem with the initial data model being too restrictive and inflexible?

Database Design – Checking for BCNF Through Functional Dependencies

First of all, you say:

(TFN → ID is redundant right?)

This dependency is not redundant, since otherwise you do not know that TFN is a key, and you lose an essential information. So you must include it in the set of functional dependencies.

For phone, if each phone is uniquely associated to a person, and each person can have only one phone, then you have these functional dependencies:

ID → TFN
ID → Name
ID → Phone 
TFN → ID
Phone → ID

There are three keys, ID, Phone and TFN, and the relation is in BCNF.

However, if each person can have more than one phone, than the situation is completely different. You have this set of functional dependencies:

ID → TFN
ID → Name
TFN → ID
Phone → ID

the key now is only Phone, and the relation has redundancies (since for each phone related to the same person, you have to repeat the ID, TFN, and Name of that person). The relation is not in BCNF, and to bring it in such form you have to decompose it in the two relations:

R1(ID, TFN, Name)
R2(Phone, ID)

the first with the dependencies:

ID → TFN
ID → Name
TFN → ID

and the second one with the dependency:

Phone → ID

so that no dependency is lost with the decomposition.

Best Answer

Related Solutions

Why Apply Constraints in Database Rather Than Code?

Database Design – Checking for BCNF Through Functional Dependencies

Related Question