Data model: parent and child 'dual' relationship

Question

I am trying to create a proper parent/child relationship within my data model. I have a typical one to many relationship between the parent and children.

I am wondering if I have parents that know about their children, is it 1) ever acceptable and 2) a good idea for each child to specifically know about its parent. (a child can only have one parent in my case)

parent      
-------------
PARENT_ID
OTHER_COL
...

child
-------------
CHILD_ID
PARENT_ID    // <-- Should this column be here?
OTHER_COL
...

parent_has_children
--------------------
PARENT_ID
CHILD_ID

The advantage I see for having the parent column in the child, is for easily retrieving the parent from a child. But, is this just lazy design?

Thanks in advance.

Answer 1

If the relationship is truly one-to-many and not many-to-many then you would leave PARENT_ID on the CHILD table as the foreign key and drop the PARENT_HAS_CHILDREN table altogether.

Answer 2

If the relationship is 1 parent to many children, then the standard model is just to FK the Parent from the Child.

// <-- Should this column be here? = Yes

i.e. no need for the Many : Many table.

If you have an entity model in an application, the parent entity usually has a collection for the children. This will be loaded based on the FK

i.e. SELECT ... FROM Child WHERE ParentId = Me

Answer 3

I would tend towards scrapping the parent_has_children table and instead just have a PARENT_ID on a child - this is the more conventional way of representing this data.