Hibernation, SQL, and recursive associations

Question

There are two tables in my database: “question” and “field”. Questions can have many fields, and fields can have many fields. This is a tree with a special root node.

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

What is the best solution for this?

• add question_parent_id and field_parent_id to the "field" table, and use only question_parent_id if it is a direct descendant. (check XOR, SQL constraint ... may depend on SQL server)
• add question_parent_id and field_parent_id and always use question_parent_id. Remember to stay consistent ... (question_id should not change, maybe not a real risk)
• Use the specific inheritance of the postgresql table: the “question” and “field” extends the “content”, so a single foreign key column is enough. Use an additional restriction for both the “question” and the “field”.
• use a third table (called a "container") consisting of only an identifier. Containers can have many fields, and a field can have one container. Questions have one container. But this requires additional code in java and has the risk of endless loops, even with a unique key on field_container_id ...

Answer 1

I would think of a class model, not a relational model. The user at the end does not care about how many keys you have in your database (usually). It uses your classes, where it should be "simple and easy to use." So first write your class model and think about how it will be displayed later.

The solution in the database depends on your class model.

Change Your model, on the other hand, depends on what you need to do.

Navigation: Do you usually need all the fields from the question? Usually you only need fields that are directly related to the question or field, or all fields recursively down the tree? Do you need to know the parent of the field? etc.

Requests Do I need to filter questions or fields by the fields assigned to them? Recursive? Do I need to filter the fields with the parent? and etc.

In other words: you cannot optimize everything. There are typical queries and typical navigation paths. Supporting too many methods can become costly and may require redundant data both in the model and in the database, making it difficult to maintain.

Answer 2

If I didn’t miss something, you have a one-to-many relationship between [Question] and [Field] (this is one-to-many, right?) And an independent link to the one-to-many relationship between [Field] . Therefore, I would:

• add question_id to the [Field] table for the previous relationship
• add parent_id to the [Field] table for a later relationship

Hibernate can display this without any problems.

Answer 3

You should carefully consider how deep your hierarchy is if you want a solution for performers. These types of recursive structures can be extremely expensive to load hibernate, since it often leads to the creation of a large number of joins for each field, since it can have child fields (and they can have child fields, etc.).

If you want to allow infinite depth, but you always want to download the whole question, including all fields and subfields. Then I would suggest that the field has a parent field (with a zero value) and a question with the owner (non-empty). This allows you to efficiently load the whole question with the following HQL:

 SELECT q FROM Question q JOIN FETCH q.allFields