How to Model Data with Parent/Child Relationships

data structuresdatabase-designmodeling

I think it is just my thinking that is off here. Suppose I have two entities: question and choice. A question can be a parent or child, or both (in relation to other questions). It seems that this model is too simple and is an unresolved many-to-many, but I think my thinking is the problem here so this is more of a conceptual question. What is the best way to design/model this? Please explain why it is or isn't a many-to-many relationship, and whether or not it needs to be resolved and why. See below:

idea

A question can be a parent or a child, or both (potentially multiple times). Programming logic would prevent the child from being its own parent and vice versa. It seems I'm missing a linking solution of some sort here, or I'm just not wrapping my brain around this.

Best Answer

If a child can only have 1 parent it's not a many-to-many relationship, it's just 1 to many.

You could do this by simply adding a parent_id to the question:

Question: - id - parent_id (Contains the id of a parent if this is a child) - title - text

This is all you need, to get all childs to a parent just query for all questiong which have that specific parent_id. To get a parent from a child just use the parent_id column.

Related Topic