Hibernate, SQL and recursive associations

Question

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...

Answer 1

Unless I'm missing something, you have a one-to-many relation between [Question] and [Field] (it's a one-to-many, right?) and a self referencing one-to-many association between [Field]. So I would:

• add a question_id to the [Field] table for the former relation
• add a parent_id to the the [Field] table for the later relation

Hibernate can map this without any problem.

Answer 2

I would rather think about the class model than about the relational model. The user at the end doesn't (usually) care how many keys you have in your database. He is using your classes, where it should be "straightforward and simple to use". So write your class model first and think about mapping later.

The solution in the database depends on your class model.

Edit: Your model on the other side depends on what you need to do.

Navigation: Do you usually need all fields from a question? Do you usually need only the fields directly assigned to a question or field or all field recursively down the tree? Do you need to know the parent of a field? etc. etc.

Queries: Do you need to filter questions or fields by fields assigned to them? Recursively? Do you need to filter fields by parent? etc.

In other words: You can't optimize for everything. There are typical queries and typical navigation paths. Supporting too many ways could become expensive and could require redundant data in both the model and the database, which makes it hard to maintain.

Answer 3

You have to carefully consider how deep your hierarchy is going to be if you want a performant solution. These kind of recursive structures can be extremely expensive for hibernate to load since it will often end up creating a large number of joins for each field because it might have child fields (and they might have child fields etc etc.)

If you want to allow infinite depth but you're always wanting to load the whole question including all fields and sub-fields. Then I would suggest Field has a parent field (nullable) and an owning question (non-nullable). This allows you to efficiently load the entire question with the following HQL:

SELECT q FROM Question q
JOIN FETCH q.allFields