C# Composite pattern and Database Access

Question

Suppose I have a composite class PharmaProduct(which represents the product hierarchy of a pharmaceutical company) and a database table for it. I have thought two ways to load the data into a PharmaProduct object.

(1) Construct the entire object-tree when an object is instantiated. Make changes to the tree and persist those changes by applying recursive loop to the tree (This is actually the way C# DataSet works).

(2) Load a node. Load other nodes only if

PharmaProduct GetParent()

or,

List<PharmaProduct> GetChldren()

are called (which actually do the direct database access). Make change to the node. Only save that node.

This type of tables may have a thousand entries, depending on how many types of items a pharmaceutical company manufactures. So in that case, the 1st approach will be too clumsy (and also memory consuming) I think.

How should I actually do the database access in case of any Composite Pattern problem?

Answer 1

Take a look at the Proxy pattern. Using it, you would put PharmaProductProxy objects in the tree that have the same interface as PharmaProduct, but lazy load themselves when they are accessed.