lookup parent, grandparent, or great-grandparent?

[LaRetta]

Hi everyone!

This has come up before so I wanted to throw it out for opinion if you would be so kind:

I have standard one-to-many through several tables (which are tiers) as per attached. Normally Answers would only need the QuestionLabelID and would not need the QuestionID, SubCategoryID, CategoryID or AreaID because that information is available through the relationship. But sometimes one of the 'lower level' tables (which usually holds the most records, such as LineItems) will need an ID from up the chain.

My question is ... is it better to A) place that AreaID also into Sub-Category as lookup from Categories and then Questions as lookup from SubCategories, then in QuestionLabels as lookup from Questions so Answers looks up AreaID from its immediate parent of QuestionLabels? It would mean additional redundant data (the AreaID in the other tables) but drive space is cheap. Or is it more efficient to just perform a lookup from great-great-great-grandparent AreaID directly into Answers::AreaID?

So is there any benefit from looking up from immediate parent over looking up through five relationships? Importing can really slow with a lot of auto-enters. Would speed change depending upon which technique was used? :)

[LaRetta]

Another thing I have done is use the relationship itself to populate additional foreign keys by including them in the primary relationship (when 'allow creation' is on). Script or script trigger would be the last option. I suppose my question is quite broad but thoughts (even theories and suppositions) are appreciated. And if anyone has performed speed tests, that would totally rock!

I suppose I should just add it to my long list of things to test. :laugh2: