Should the grandparents' foreign keys be stored in the grandchildren's table?

Question

What are the benefits and obligations of including grandparent + foriegn keys in a table.

For example, if my object model looks below. (Significantly simplified, so it is not suitable for a reference recursive table.)

a {aId, bCollection, ...} b {bId, cCollection, ...} c {cId, dCollection, ...} d {dId} 

The two data model options that come to mind are as follows:

option 1:

 a {pkA, ...} b {pkB, fkA, ...} c {pkC, fkB, ...} d {pkD, fkC, ...} 

option 2:

 a {pkA, ...} b {pkB, fkA, ...} c {pkC, fkB, fkA, ...} d {pkD, fkC, fkB, fkA, ...} 

Option 1 is more normalized, and inserts and updates will be easier, but I see that the queries are becoming quite complex, especially with many, many relationships and / or compound keys.

Option 2 complicates insertions and updates, but extracting reports will be easier. In addition, the database will be larger, but in fact it does not bother me at all, since it is quite small.

But these are pretty minor issues compared to issues that can arise with an ORM infrastructure similar to an entity. I am inclined to option 2, because I would like to access the grandchildren directly from the parent, for example:

 Class A { id, bCollection, cCollection, dCollection, ... } Class B { id, cCollection, dCollection, ... } Class C { id, dCollection, ... } Class D { id, ...} 

Is framework 4.0 entity handling this situation gracefully? What are the pros and cons of the two options? Is there any other alternative that I should consider?

Or simply put, how the hell does one google ask this question ?!

One more note. Like many of you, you need to rely heavily on Option A for your brush and head, but I know that I read an article in msdn that details why Option B is better. Sorry, I can not find it. :(

in advance for your thoughts.

Answer 1

I would avoid option B like the plague. None of the options is more difficult to query than the other, but the second option is much more difficult to maintain and shouts for normalization in option A.

To request option A, all you say is adding simple joins. Since this is not a recursive relationship, you should already know how many levels of deep query can go, so you don’t need to worry that it is fragile or works only for a subset of potential cases.

Compare the selection of the deepest case when you are looking for a top-level parent of a deeply nested node:

Option A:

 select a.id from d join c on c.id = d.c_id join b on b.id = c.b_id join a on a.id = b.a_id where d.id = @id 

Option B:

 select a_id from d where id = @id 

Is option A more complicated? Yes, but this should not challenge anyone to understand what is happening.

As for compatibility with EF4, this is not a problem. You can navigate the chain of parents in a linear way to get the desired grandparents. You can do this in code or in a request; either one will work fine:

In code:

 var entity = context.D.Where(d => d.Id == 4).First(); var grandParent = entity.CBA; 

In the query (using LINQ and joins):

 var grandparent = (from d in context.D join c in context.C on d.CId == c.Id join b in context.B on c.BId == b.Id join a in context.A on b.AId == a.Id where d.Id == id select a // or a.Id).First(); 

In the request (using navigation properties):

 var grandparent = (from d in context.D where d.Id == id select dCBA // or dCBAId).First(); 

Answer 2

If your database is mostly OLTP, I would go with option A. If your messages become a problem, you can create a view or a denormalized table to represent option B.

If your database is mostly OLAP, I would go with option B.

Answer 3

I would say that if the database has good support for recursive relationships (that is, Oracle does this very well, and some others have specific syntax for these queries), then use recursive (your option A). Otherwise, you will be delayed for several days, debugging your code, making sure that you update all the records that need to be updated when the relationship changes, and you need to manually cascade your changes.

And for Google, use the term "recursive tables" or "recursive queries."