Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Say we want to enforce that a table represents a tree. I have found this method for doing so:

create table tree (
  id integer[] primary key,
  parent integer[] references tree (id),
  item text not null,
  constraint tree_parent check (
       (id = array[]::integer[] and parent is null)
    or parent = id[1:array_length(id,1)-1])
);

A node in the tree is identified by its path from the root node. As such, the foreign key parent is just that path with one element dropped off the end. The combination of foreign key and check guarantees the tree structure.

First question: is this a good idea? Are there better approaches? In Oracle at least, I'm able to define parent as a virtual column, which is elegant.

Second question: is there a name for this approach? I like to think of this as a "functional foreign key": tree.id references tree.id, but via a function that drops the last element in the path. I think this concept has more general use than the tree example above.

share|improve this question
1  
This seems like it might be a better question for a different StackExchange site. Perhaps try the Database Administrators one? –  Corion May 7 '13 at 15:16
add comment

1 Answer

1) Can you explain better your check constraint. What exactly you want to check? Circles?

If you going to check the whole path for item on INSERT this can cost good time ... each jump means indexes search of a key.

2) I do not see how you can make 'parent' to be a virtual column, i.e. calculated column ... It will be calculated from where?

I think the 'parent' column is required to save on disk link to a parent item.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.