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 want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as questions on this site, children as answers, and favorite as the accepted answer.) For example,

TableA
    Id            INT PRIMARY KEY

TableB
    Id            INT PRIMARY KEY
    Parent        INT NOT NULL FOREIGN KEY REFERENCES TableA.Id

The way I see it, I can either add the following column to TableA:

    FavoriteChild INT NULL FOREIGN KEY REFERENCES TableB.Id

or the following column to TableB:

    IsFavorite    BIT NOT NULL

The problem with the first approach is that it introduces a nullable foreign key, which, I understand, is not in normalized form. The problem with the second approach is that more work needs to be done to ensure that at most one child is the favorite.

What sort of criteria should I use to determine which approach to use? Or, are there other approaches I am not considering?

I am using SQL Server 2012.

share|improve this question
    
@reinierpost Of course every child has only one parent. That's what the Parent FOREIGN KEY REFERENCES TableA.Id tells us. –  ypercube Feb 5 '13 at 17:42
    
@ypercube ... but of course. thanks –  reinierpost Feb 5 '13 at 17:59

3 Answers 3

up vote 8 down vote accepted

Another way (without Nulls) is to have a third table to store the "favourite children". In most DBMS, you'll need an additional UNIQUE constraint on TableB.

@Aaron was faster to identify that the naming convention above is rather cumbersome and can lead to errors. It's usually better (and will keep you sane) if you don't have Id columns all over your tables and if the columns (that are joined) have same names in the many tables that appear. So, here's a renaming:

Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    UNIQUE (ParentID, ChildID)

FavoriteChild
    ParentID        INT NOT NULL PRIMARY KEY
    ChildID         INT NOT NULL 
    FOREIGN KEY (ParentID, ChildID) 
        REFERENCES Child (ParentID, ChildID)

In SQL-Server (that you are using), you also have the option of the IsFavorite bit column you mention. The unique favourite child per parent can be accomplished via a Unique Index:

Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    IsFavorite      BIT NOT NULL

CREATE UNIQUE INDEX is_FavoriteChild
  ON Child (ParentID)
  WHERE IsFavorite = 1 ;

And the main reason that your option 1 is not recommended, at least not in SQL-Server, is that the pattern of circular paths in the foreign key references has some problems.

Read a quite old article: SQL By Design: The Circular Reference

When inserting or deleting rows from the two table, you'll run into the "chicken-and-egg" problem. Which table should I insert first - without violating any constraint?

In order to solve that, you have to define at least one column nullable. (OK, technically you don't have to, you can have all columns as NOT NULL but only in DBMS, like Postgres and Oracle, that have implemented deferrable constraints. See @Erwin's answer in a similar question: Complex foreign key constraint in SQLAlchemy on how this can be done in Postgres). Still, this setup feels like skating on thin ice.

Check also an almost identical question at SO (but for MySQL) In SQL, is it OK for two tables to refer to each other? where my answer is pretty much the same. MySQL has no partial indexes though, so the only viable options are the nullable FK and the extra table solution.

share|improve this answer

It depends on what your priority is. Do you want to avoid work or do you want to adhere to the strictest rules of normalization?

Personally, I think it is better to have IsFavorite in the child table, and would be willing to put in the work to make sure that at most one child for every parent is that parent's favorite. The primary reason has nothing to do with the nullable foreign key thing: I don't like the idea at all of foreign keys pointing in both directions.

@ypercube's suggestion is a good compromise as well.

As an aside, please, please, please don't litter your schema with meaningless column names like Id. I would much rather see the Id be named in a meaningful way throughout the schema. Does it identify an author? Ok, call it AuthorID. Does it represent a product? Ok, ProductID. Is it an employee, and in some cases references a manager? Ok, EmployeeID and ManagerID make more sense to me than ID and Parent. While it may seem logical to leave that out (and redundant to put it in), when you start writing complex joins (or post queries here) you will definitely sense some cursing when you're trying to reverse engineer a bunch of joins that point to columns like a.Parent = b.ID ... blecch.

share|improve this answer
    
Naming columns Id is a sql antipattern. amazon.com/SQL-Antipatterns-Programming-Programmers-ebook/dp/… –  HLGEM Feb 5 '13 at 15:42

Data belongs in the child table. We keep it correct with a trigger on the table to ensure one and only record is marked as favorite (or in our case as the preferred address).

However, @ypercube's idea of a separate table is a good one as well.

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.