hibernate java orm sql tree

Hibernate, SQL and recursive associations

My database has two tables, “question” and “field”. Questions may have many fields, and fields may have many fields. It’s a tree with a special root node.

I want to use them with hibernate (currently potgresql) – so it should be straightforward and simple to use it from java.

What is the best solution to this?

  1. add question_parent_id and field_parent_id to “field” table, and only use question_parent_id if it is a direct descendant of it. (check XOR which SQL constraint… may depend on SQL server)
  2. add question_parent_id and field_parent_id, and always use question_parent_id. Remember to stay consistent… (question_id should not change, probably not a real risk)
  3. Use the postgresql specific table inheritance: “question” and “field” extends “content”, so one foreign key column is enough. Use additional constraint on both “question” and “field”.
  4. use a third table (called “container”), consisting of just an id. Containers may have many fields, and a field may have one container. Questions have exaclty one container. But this requires additional code in java, and has a risk of infinite loops, even with an unique key on field_container_id…