How do I best represent a one-to-many relationship where one child is a special case?

Question

I have a schema with a parent-child relationship between two entities. It's a one-to-many, so would naturally be implemented as:

table Parents
  id

table Children
  id
  parent_id

However, I also need to treat one of the children as a special case (Let's call it the "promoted" child). I can think of several ways to do this, including:

Just put an attribute on Children:

table Parents
  id

table Children
  id
  parent_id
  is_promoted

which is obviously flawed, in that the database can't guarantee consistency (At least not through regular foreign key constraints)

Create a foreign-key on the Parents table:

table Parents
  id
  promoted_child_id

table Children
  id
  parent_id

Which was my first inclination, but it does have the obvious downside of a cyclic dependency, so probably not optimal.

Another option, I can think of is to put a second foreign-key on the Children table:

table Parents
  id

table Children
  id
  parent_id
  promoted_parent_id

Which allows me to place a unique index, thereby enforcing database consistency. One problem with this, is that it would be possible to have a meaningless relationship, where a child of parent A lists as promoted of parent B.

The last option I can think of, is to create an intermediary table, e.g.:

table Parents
  id

table Children
  id

table ParentChildRelationship
  parent_id
  child_id
  is_promoted

Again, I can declare a unique constraint on parent_id+is_promoted, enforcing consistency. I'm a bit ambivalent on this one, since it seems like overkill to promote the relationship to a full entity, although the moment I put attributes on it (Which essentially is_promoted is), I guess it's warranted.

I'd like to know what you would consider the canonical way to handle this problem. In particular, I'm using Rails, so that may influence on what is the most practical solution.

Rosariarosario

In the first case, why don't you want to use a unique constraint applied on both parent_id and is_promoted columns ? – Hardden

@jef That sounds like a really good suggestion. I have no idea why I didn't think of that. – Rosariarosario

@Hardden a unique constraint on (parent_id, is_promoted) would only permit 2 children per parent; a promoted and a non-promoted. – Immerge

@bmewburn That depends on how the database enforces uniqueness. Oracle for example, never treats one null as being equal to any other null (see this). – Inclusion

@bmewburn I just realised that as well. I could then implement the column with null meaning false and any value meaning true. But that would be at odds with how Rails do things. – Rosariarosario

Answer 1

I don't have any rails experience but have come across similar when handling default values for 1 to many relationships and have usually gone with something like this for db schema:

table Parents
  id

table Children
  id
  parent_id
  FK (parent_id) references Parents(id)

table PromotedChildren
  child_id
  parent_id
  FK (child_id, parent_id) references Children(id, parent_id)
  UNIQUE Key parent_id

Edit

After answering this it got me thinking why I prefer this representation over the others, so I did a bit more research and gave it some more thought:

table Parents
  id

table Children
  id
  parent_id
  is_promoted

This with a unique constraint on (parent_id, is_promoted) as proposed by Jef would work if nulls are ignored by the db, as suggested by Kelvin, and may be a good option. However, some might argue that using null as a substitute for false/0 is incorrect usage of null and it effectively turns the field into tri-state ie 1, 0, unknown. Also (parent_id, is_promoted) is not a superkey and as such violates Domain Key Normal Form.

table Parents
  id
  promoted_child_id

table Children
  id
  parent_id

As you pointed out this may cause cycles and by making promoted_child_id an FK that references Children(id) you still are at risk of database anomalies where ParentA lists ParentB's child as promoted.

table Parents
  id

table Children
  id
  parent_id
  promoted_parent_id

Here you get the risk of anomalies as described; a unique key on promoted_parent_id must also rely on the db ignoring nulls; multiple cascade paths; redunancy as promoted_parent_id and parent_id should be the same; update anomalies where parent_id does not match promoted_parent_id.

table Parents
  id

table Children
  id

table ParentChildRelationship
  parent_id
  child_id
  is_promoted

This looks more like a many to many representation of parent to child. It presents similar problems to the others above.

As to what is most practical for rails development I cant say. I have used both the representation I put forward and Jef's with C# and PHP and haven't really noticed a difference.

Immerge

That's a possibility I didn't consider. It seems to have the same flaws that putting an extra fkey on the Children table - namely that you could get an inconsistent reference, where Parent A has a Child which is a promoted child of B. – Rosariarosario

@troelskn, maybe I'm not understanding your requirements but if parent to child is 1 to many then having a third table of promoted children with a fk of the {id, parent_id} subset of the children attributes does not permit the inconsistency you describe. – Immerge

Ah .. I didn't consider that PromotedChildren could refer to Children(id, parent_id) - I assumed you meant that PromotedChildren.parent_id would refer directly to Parent.id. That's quite clever. – Rosariarosario

@bmewburn Pretty interesting insight, thanks for your detailed answer. As far as I can tell, all solutions should work well with ActiveRecord/Rails, the question is IMO more related to database. – Hardden

Answer 2

You may apply a unique constraint on both parent_id and is_promoted columns to ensure integrity and avoid the drawbacks of the two other solutions you mentioned. It should perform quite well also when querying.

Hardden