sql - Interesting tree/hierarchical data structure problem

Question

Colleges have different ways of organizing their departments. Some schools go School -> Term -> Department. Others have steps in between, with the longest being School -> Sub_Campus -> Program -> Term -> Division -> Department.

School, Term, and Department are the only ones that always exist in a school's "tree" of departments. The order of these categories never changes, with the second example I gave you being the longest. Every step down is a 1:N relationship.

Now, I'm not sure how to set up the relationships between the tables. For example, what columns are in Term? Its parent could be a Program, Sub_Campus, or School. Which one it is depends on the school's system. I could conceive of setting up the Term table to have foreign keys for all of those (which all would default to NULL), but I'm not sure this is the canonical way of doing things here.

Answer 1

I suggest you better use a general table, called e.g. Entity which would contain id field and a self-referencing parent field.

Each relevant table would contain a field pointing to Entity's id (1:1). In a way each table would be a child of the Entity table.