Populating nested collections from database

Question

I have the following 3 classes

Book
Product
SpecialOptions

There are many Books, and there are many Products per Book. Likewise in a Product there are many SpecialOptions. There are other properties of each of these three classes so each class has the following interface

Public Interface IBook
   Private ProductList() as collection (of Products)
   Private Somethingelse() as String
End Interface

Public Interface IProduct
   Private SpecialOptionsList() as collection (of SpecialOptions)
   Private Somethingelse() as String
End Interface

Public Interface ISpecialOptions
   Private SpecialOptionsProperty() as String
End Interface

I want to create a collection of Books, which has each of the products under it, and under each of those Products I have want the associated SpecialOptions, when I pull the data out of a database. I can't decide which will be the best way to do this.

I have two methods. Either I go from the top down or from the bottom up. Meaning, I start with a book and then fill out the product information and then for each of those products fill out the detail information. OR I can get the details first, and then add them to the appropriate product and then do it again for products to books. Neither of these is very appealing.

Also, and because I suggested it to myself when proofreading, this is the structure that I need to capture the actual relationship, so reframing the problem with different structure is not going to work.

Answer 1

Hi Anthony,

You said

...start with a book and then fill out the product information and then for each of those products fill out the detail information...

I would start with the book, then drill down. This will allow you to filter on only the necessary information.

I hope that answers your question.

Answer 2

There are a few ways to approach the problem:

Load all the books, then when creating each Book object, load all the Products, then while creating each Product object, load the special options. This will cause a large number of queries to the database, but is simple and keeps the DB Sprocs simple. Psudo code:

foreach bookR in GetBooks
    Add a new book to the book collection
    foreach product in GetProductByBook
        Add a new product to the book's product collection
        foreach option in GetOptionByProduct
            Add a new option to the product option collection

---

Load all the books, all the book's products and all the product's options in the one Sproc, returning 3 result sets. Then first create all your books, then create your products by finding the correct book in your book collection and adding it there. Same for your products. Psudo Code:

foreach bookR in GetResultSet1
    Add a new book to the book collection
foreach productR in GetResultSet2
    Find the correct book in book collection
    Add a new product to the book's product collection
foreach option in GetResultSet3
    Find the correct book in the book collection
    Find the correct product in the book's product collection
    Add a new option to the product option collection

---

Bring back all the data in one result set (LINQ-to-Sql does it this way). Join all three tables together and bring back one result set. Itterate through each row, check whether a book matching that row exists (if not create it), then check whether a product on that book exists (if not create it). Psudo Code:

results = GetResultSet
foreach result in results
    get the book for matching result book id
    if the book does not exist, create it from result
    get the product on the book for the matching result product id
    if the product does not exist, create it from result
    etc