Sean:
I certainly do agree that we're doing more work on the inserting new rows to Child tables and that also allows for more errors (e.g. trying to insert wrong grandparent ID due to a bug in the application).
However, I hope you forgive me for saying that but I was hoping for more than "nobody has done it so it must be wrong". After all, it didn't stop Codd when he first formalized what we now know as normalization. 😉
Normalization are quite clear-cut because we can see the negative consequence of update anomalies created when normalization is violated. The only reason to denormalize is really due to physical limitations and even so it's mightily an iffy proposition in OLTP realm. For the above schema, the only valid reason (to me at least) is to be able to create an index on the Child that correlates to the GrandParent records and having enough of cardinality to make a difference... in theory.
For example, we could compare those queries:
Traditional approach:
SELECT c.Customer, o.*
FROM dbo.Customers AS c
INNER JOIN dbo.Accounts AS a
ON c.ID = a.CustomerID
INNER JOIN dbo.Orders AS o
ON a.ID = o.AccountID
WHERE c.Inactive = 0
AND c.CustomerType = 1;
Alternative approach:
SELECT c.Customer, o.*
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
ON c.ID = o.CustomerID
WHERE c.Inactive = 0
AND c.CustomerType = 1;
Under traditional approach, we'd need to use indices on Order's AccountID and on Account's CustomerID to effectively process the results whereas the alternative approach would use only single index. That would certainly mean less pages to access in returning the results, right? Or am I missing factors/other details that may make this unnecessary or overstating the benefits?
capnhector:
I absolutely agree that denormalization shouldn't be the first choice. My intention was primarily to find out if someone else thought of that already and see what experience was like. Sean's suggestion that having to asking suggests that it may be wrong is typically correct but I didn't just want to simply assume. 🙂