Database Parent -> Child "firstborn" relation

Question

I have a database-design problem. To illustrate my problem imagine the following:

A simple parent->child relation:

[Parent] 1-----* [Children]

Now every Parent (mum or dad) has a firstborn child when there are any children. I need a strong relation in database from Parent to the Firstborn-Child

[Parent] 1-----1 [Children]

I could simply add a FirstbornChildId to the Parent Table, but i do not like this approach since It could result in inconsistent data. (FirstbornChildId links to a Child which ParentId links to a different parent...)

Entity Framework should map the relation, so that every Parent-Entity has a FirstbornEntity property linking to the specific Child.

Is there any nice way to achieve this in Microsoft SQL 2008 R2?

Answer 1

I think your approach is fine. You'll have to prevent data corruption in the application layer. There is only so much you can do with database constraints. You could at least have a foreign key for FirstbornChildId to make sure that the child does indeed exist (regardless of parent).

This "firstborn" attribute is denormalization anyway: it can be derived from having a "birthdate" attribute on the children. I appreciate that you want to store it explicitly for performance reasons, and there is no problem with it. But if you want to have the database handle integrity completely, this does make things tricky.

Answer 2

Two possible solutions

Three tables:

• Parent table
• Children table
• FirstBorns table
  • id in parent
  • id in children

or

Column WasFirstBorn in Children table (Y/N or 1/0)