Best SQL approach for populating composite Business Objects
Well, I didn't get much response out of CFCDev, so I figured I would move the discussion over to my blog.
(So applogies to all this sounds really familiar to)
I always wonder what the best way is to get the results from a database when dealing with composite Business Objects.
Reading Matt Woordards blog post on DAOs and Composition,
He talks about composition, and to quote:
--
We're using composition in our beans, so why not use composition of
sorts (this isn't strictly composition, but bear with me ...) in our
DAO as well? When we instantiate the Person DAO, why not just
instantiate an Address DAO inside the Person DAO so we can call things
that way?
--
Now, in my mind - there are two options for getting the PersonBO information and the AddressBO information.
1) Seperate SQL queries for both BO's - The Address DAO gets the results for a Address, and the PersonDAO gets the results for the
Person BO - there are 2 query hits on the db.
Pros: It's clean, consise and really reusable
Cons: For complex objects, that's alot of database hits - is that a good idea?
2) JOIN between tables - a Gateway object (maybe?) gets a joined result by getting the relevent data form both the Person Table and the
Address Table, and the DAO's intelligently create the BO's that they need from that single query's results
Pros: It's alot more efficient than the above
Cons: It doesn't exactly promote code reuses
It also can get tircky as JOIN can cause duplicate data to display, and you have to avoid creating the same BO twice or more (unless you want to join up data usion UNION or something similar).
Personally I've been running with option (2) for a while, simply from a database performance perspective - but it does leave me with alot of pretty ugly code. Which makes me lean more these days towards options (1)
But sometimes I write BO's that can be composite of greater than 20 objects (and yes, all are required), so querying the database will 20 small hits I think would take a longer time.
So, which way around do you do it? and why?





