Categories
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…