Linking grandchildren to grandparents

[timbr8ks]

A quick question. Say you're constructing a database that has these tables:

• book
• book_section
• section_question
• question_answer

Each table has an id field serving as the primary index (e.g., book_id, book_section_id, etc.). Also, each section is linked to it's parent. So, book_section starts with these two fields:

• book_section_id
• book_id

The question is, should each be linked ONLY to it's parent? Or should grandchildren be linked to their grandparents? If so, the table question_answer starts with these fields:

• question_answer_id
• book_id
• book_section_id
• section_question_id

I can see down the road a reason to do this. The question, I guess, is whether there's a reason not to do this. Thoughts?

Thanks.

[andyman97]

In my humble experience I have found that it is better practice to link to the parent only. One seems to get into a lot less problems that way.

So what you do is have a function called getparent($child)

And then you great a functionlike this:

function getgrandparent($grandchild){
return getparent(getparent($grandchild)));
}

I guess it is even better to have a function that get's the nth parent, like so:

// Returns the nth parent of the parsed child
function getnthparent($child,$n){

          $newchild=$child;
          for($i=1;$i<=$n;$i++){
                       $newparent=getparent($newchild);
                       $newchild=$newparent;
          }
          return $newparent;

}

I think the above works - I haven't tested it - I may have a bug in the logic here and there. The aim is that to get the parent you would type:
getnthparent($child,1)
To get a grandparent you type:
getnthparent($child,2)

Hope that helps.

Andyman

[tomhath]

I agree with andyman97; you're guessing that someday there will be a good reason (performance or simpler logic) to make that connection now. I've seen all kinds of ugly database designs made on the basis of that kind of guess. And the guess usually backfires, maintaining multiple links denormalizes the database and really complicates maintenance. Usually for little if any benefit. If you really need it you can always add the column later, but once it's crept into your code you can never get rid of it.

[dniry]

That makes it unanimous... one hardly ever de-normalizes relational tables. If you wish to enhance your database performance for queries that require many inner joins, you can always create an sql view:

create view GrandChildGrandParentView as
select a. question_answer_id, d.book_id, d.book_name etc...
from question_answers a, section_question b, book_section c, book d
where a.question_answers_section_question_id=b.section_question_id
and b.section_question_book_section_id=c.book_section_id
and c.book_section_book_id=d. book_id

you can then query the view as you would a regular table:

select * from GrandChildGrandParentView where...

Note that views can only be used in select statements on most DBMS flavors (no update / insert). they also work best if you have indexes on your foreign key columns. this will work in oracle or sql server. mysql should have it in version 5 I heard.