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?
- 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)
- 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)
- 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”.
- 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…