What is the best idea of ​​a one-to-many relationship when one child is a special case?

Question

I have a schema with a parent-child relationship between two objects. This is one to many, therefore, of course, it will be implemented as:

table Parents id table Children id parent_id 

However, I also need to consider one of the children as a special case (let him call him an "advanced" child). I can come up with several ways to do this, including:

Just put the attribute on Children :

 table Parents id table Children id parent_id is_promoted 

which is clearly wrong because the database cannot guarantee consistency (at least not with foreign key constraints)

Create a foreign key in the Parents table:

 table Parents id promoted_child_id table Children id parent_id 

What was my first slope, but it has an obvious lack of cyclic dependence, so it’s probably not optimal.

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

 table Parents id table Children id parent_id promoted_parent_id 

Allows me to place a unique index, thereby ensuring database consistency. One problem is that one might have a meaningless relationship, where the child of the parent A lists as the advanced parent B.

The last option that I can think of is to create an intermediate table, for example:

 table Parents id table Children id table ParentChildRelationship parent_id child_id is_promoted 

Again, I can declare a unique restriction on parent_id + is_promoted to ensure consistency. I am a bit ambivalent in this matter, since it seems that it is unnecessary to encourage the attitude towards the complete entity, although the moment I add attributes to it (which, in essence, is_promoted ), I assume that it is justified.

I would like to know what you think is the canonical way to solve this problem. In particular, I use Rails, so this may affect the most practical solution.

Answer 1

I have no experience with rails, but when dealing with default values ​​from 1 to many relationships, they occur the same way, and as a rule, they have something like this for the db scheme:

 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

In response to this, I wondered why I prefer this view over the others, so I did a little more research and thought:

 table Parents id table Children id parent_id is_promoted 

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

 table Parents id promoted_child_id table Children id parent_id 

As you pointed out, this can lead to loops, and by making a promoted_child_id FK that refers to Children (id), you are still at risk of database anomalies when ParentA lists the parent element of ParentB as promoted.

 table Parents id table Children id parent_id promoted_parent_id 

Here you get the risk of anomalies as described; the unique key on promoted_parent_id should also rely on db ignoring nulls; multiple cascade paths; redunancy as promoted_parent_id and parent_id must 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 

It is more like a many, many representation of a parent to a child. This presents a similar problem to the others above.

As for what is most practical for rail development, I cannot say. I used both the presentation I put forward and Jef with C # and PHP, and didn’t really notice the difference.

Answer 2

You can apply a unique constraint to the parent_id and is_promoted columns to ensure integrity and avoid the disadvantages of the other two solutions you mentioned. It should work well on request.