Hello, I'm trying to figure out best practices for retrieving data with one-to-many relationships in an N-tier architecture. I'm using VB.NET in a Web application (both are relatively new to me). Everything looks clean if I just use customer, but I have customer_address (multiple records per customerID), customer_schoolhist (multiple records per customerID), and several other tables with one-to-many relationships with customer.
As an example, if I want to retrieve "Full" customer profiles for customers within a certain radius of a particular zip code, I've come up with several options:
1) Create a stored proc for each table. Have BOL and DAL object for each table. Seems to be very bad from a performance perspective - essentially, I would have to take several trips up and down through the layers to get my data.
2) Create one stored proc which returns all combinations of the one-to-many relationships in one result set. One trip down to the data level, one query, a ton of repeated data. Within my DAL, parse through the result set and populate my business objects accordingly (for example, if there are 2 addresses and 3 schoolhist records for a customer, there will be 6 records in the resultset but my DAL code will resolve it into one customer record with 2 addresses and 3 schoolhists.
3) Create one stored proc which returns several result sets (one for customer, one for customer_school, one for customer_address, etc.). One trip down to the data level, several queries, no "extra" info returned. But then I think I'd lose the OO-ness, or would have to match up ID's in order to create the true parent-child relationships at the business object level.
...and I'm sure there are other options. This is actually a fairly simple website, and I'll be doing the maintenance myself, so I'd like a solution which a newbie like me can easily understand and maintain. After I finished writing this, I'm leaning even more strongly toward option 2, since it seems to strike a balance between decent performance and adhering to OO and N-tier principles. But let me know if anyone has thoughts/comments/suggesti
ons.